SQL - Join - Outer

In SQL, an outer join is used to combine the rows from two or more tables, including the matching and non-matching rows from both tables. The outer join is performed based on a specified condition and the result set contains all the rows from the left or right table and the matching rows from the other table(s).

There are three types of outer joins in SQL:

Left Outer Join: This join returns all the rows from the left table and the matching rows from the right table. If there is no matching row in the right table, then NULL values are returned for the right table columns.

Syntax:

SELECT table1.column1, table1.column2, table2.column1, table2.column2
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name;

Example:

Consider two tables - employees and departments:

To retrieve the details of all employees along with their department name, we can use a left outer join as follows:

SELECT e.emp_name, d.dept_name
FROM employees e
LEFT OUTER JOIN departments d
ON e.dept_id = d.dept_id;

In the above example, the left outer join returns all the rows from the employees table along with the matching rows from the departments table. Since there is no matching row for employee Bob in the departments table, a NULL value is returned for the dept_name column.

Right Outer Join: This join returns all the rows from the right table and the matching rows from the left table. If there is no matching row in the left table, then NULL values are returned for the left table columns.

Syntax:

SELECT table1.column1, table1.column2, table2.column1, table2.column2
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name = table2.column_name;

Example:

Continuing with the employees and departments tables, we can retrieve the details of all departments along with the employees working in them using a right outer join as follows:

SELECT e.emp_name, d.dept_name
FROM employees e
RIGHT OUTER JOIN departments d
ON e.dept_id = d.dept_id;

In the above example, the right outer join returns all the rows from the departments table along with the matching rows from the employees table. Since there is no matching row for department Sales in the employees table, a NULL value is returned for the emp_name column.

Full Outer Join: This join returns all the rows from both tables, including the matching and non-matching rows. If there is no matching row in one table, then NULL values are returned for the columns of the other table.

Syntax:

SELECT table1.column1, table1.column2, table2.column1, table2.column2
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

Example:

To retrieve the details of all employees and departments, we can use a full outer join as follows:

SELECT e.emp_name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d
ON e.dept_id = d.dept_id;

In the above example, the full outer join returns all the rows from both tables, including the matching and non-matching rows. Since there is no matching row for employee Bob in the departments table and for department Sales in the employees table, NULL values are returned for the corresponding columns.