What is outer join in SQL?

Question

Can anyone explain outer join and types of outer joins in SQL?

Answer 1

In SQL, outer join returns the records of both the tables that satisfy the specified join along with rows that do not meet the join condition also. There are three types of outer joins as follows:

• Left outer join
• Right outer join
• Full outer join

A left outer join returns the all records of the left table and the records of the right table that matches the join condition. The following is the syntax for left outer join:

SELECT columns_list
FROM TableA
LEFT JOIN TableB
ON TableA.column_name = TableB.column_name;

A right outer join returns all records of the right table and the records of the left table that matches the join condition. The following is the syntax for right outer join:

SELECT columns_list
FROM TableA
RIGHT JOIN TableB
ON TableA.column_name = TableB.column_name;

A left outer join returns all the records of both tables and fills the NULL when there are no matches on either side. The following is the syntax for full outer join:

SELECT columns_list
FROM TableA
FULL OUTER JOIN TableB
ON TableA.column_name = TableB.column_name;

You can check out this SQL tutorial by Intellipaat to learn inner and outer joins with examples.