Difference between INNER JOIN and OUTER JOIN
ReportPlease briefly explain why you feel this question should be reported .
INNER JOIN
An INNER JOIN returns all rows from both the participating tables where the key record of one table is equal to the key records of another table. This type of join required a comparison operator to match rows from the participating tables based on a common field or column of both the tables.
OUTER JOIN
The OUTER JOIN returns all rows from the participating tables which satisfy the condition and also those rows which do not match the condition will appear in this operation. This result set can appear in three types of format –
The first one is LEFT OUTER JOIN, in this join includes all the rows from a left table of JOIN clause and the unmatched rows from a right table with NULL values for selected columns.
The second one is RIGHT OUTER JOIN, in this join includes all rows from the right of JOIN cause and the unmatched rows from the left table with NULL values for selected columns.
The last one in FULL OUTER JOIN, in this join, includes the matching rows from the left and right tables of JOIN clause and the unmatched rows from left and right table with NULL values for selected columns.
Example:
Here is two table tableX and tableY and they have no duplicate rows in each. In tableX the values ( A,B) are unique and in tableY the values (E,F) are unique, but the values (C and D) are common in both the tables.
Here is INNER JOIN
SELECT * FROM tableX INNER JOIN tableY on tableX.X = tableY.Y;
or
SELECT tableX.*,tableY.* FROM tableX,tableY WHERE tableX.X = tableY.Y;
Output:
Here only the matching of both tableX and tableY have appeared in the result set.
LEFT OUTER JOIN
SELECT tableX.*,tableY.* FROM tableX,tableY WHERE tableX.X = tableY.Y(+)
or
SELECT * FROM tableX LEFT OUTER JOIN tableY ON tableX.X= tableY.Y
Output:
Here all the rows from tableX that is left side of JOIN clause and all the rows with NULL values for unmatched columns from tableY that is the right side of JOIN clause have appeared.
RIGHT OUTER JOIN
SELECT * FROM tableX RIGHT OUTER JOIN tableY ON tableX.X= tableY.Y
Output:
Here all the rows from tableY that is the right side of JOIN clause and all the rows with NULL values for unmatched columns from tableX that is left side of JOIN clause have appeared.
FULL OUTER JOIN
SELECT * FROM tableX FULL OUTER JOIN tableY ON tableX.X= tableY.Y
Output:
Here all the matching rows from tableX and tableY and all the unmatched rows with NULL values for both the tables have appeared.
Leave an answer