SQL OUTER JOIN
Different from the SQL INNER JOIN, SQL OUTER JOIN returns all records from both joined tables even there is no matching record found. There are three types of SQL OUTER JOIN: FULL OUTER JOIN, LEFT OUTER JOIN and RIGHT OUTER.
SQL LEFT OUTER JOIN
Suppose we join two tables A and B. Left outer join returns all records from the table A (left table) plus matching records in the table B (right table). It means the result of the SQL LEFT OUTER JOIN always contains the records in the table A (table in the left side) even no matching record found in the table B plus matching records in the table B.
Here is the Venn diagram to visualize how SQL LEFT OUTER JOIN works.
The syntax of left outer join is as follows:
SELECT * FROM table_A
LEFT OUTER JOIN table_B ON join_conditions
WHERE row_conditions
SQL RIGHT OUTER JOIN
SQL RIGHT OUTER JOIN returns all records from the table B (table in the right side), even no matching record found in the table A, plus matching records in the table A.
Venn diagram to visualize how SQL RIGHT OUTER JOIN works:
The syntax of SQL RIGHT OUTER JOIN is as follows:
SELECT column_list
FROM table_A
RIGHT OUTER JOIN table_B ON join_conditions
WHERE row_conditions
SQL FULL OUTER JOIN
SQL FULL OUTER JOIN combines results of both left outer join and right outer join therefore it returns all records from both tables.
Venn diagram to visualize how SQL Full Outer Join works:
The syntax of SQL Full outer join is as follows:
SELECT column_list
FROM table_A
FULL OUTER JOIN table_B ON join_conditions
WHERE row_conditions
In this tutorial, you’ve learned how SQL OUTER JOIN works and all kinds of SQL OUTER JOIN: SQL LEFT OUTER JOIN, SQL RIGHT OUTER JOIN and SQL FULL OUTER JOIN.