Outer Join - SQL

In SQL, an outer join is a type of join operation that includes rows from both tables, even if there are no matching rows between them based on the join condition. Outer joins are useful for retrieving data from one table regardless of whether matching rows exist in the other table.

There are three types of outer joins:

  1. Left Outer Join (or Left Join): Returns all rows from the left table (the first table specified in the JOIN clause) and matching rows from the right table (the second table specified in the JOIN clause). If there is no match, NULL values are included for the columns from the right table.
  2. Right Outer Join (or Right Join): Returns all rows from the right table and matching rows from the left table. If there is no match, NULL values are included for the columns from the left table.
  3. Full Outer Join (or Full Join): Returns all rows when there is a match in either the left or right table. If there is no match, NULL values are included for the columns from the table without a matching row.
SQL 
SELECT columns
FROM table1
[LEFT|RIGHT|FULL] OUTER JOIN table2
ON table1.column = table2.column;

Example

Consider two tables, "employees" and "departments," where you want to retrieve all employees and their corresponding departments. Some employees may not be assigned to any department, and some departments may not have any employees.

SQL 
SELECT employees.name, departments.department_name
FROM employees
LEFT OUTER JOIN departments
ON employees.department_id = departments.department_id;

This query performs a left outer join, ensuring that all employees are included in the result set, even if they are not assigned to any department. If an employee is not associated with any department, the department-related columns will contain NULL values.

Conclusion

In SQL, outer joins provide a way to retrieve data from multiple tables, ensuring that all rows from one table are included in the result set, even if there are no matching rows in the other table. By understanding the syntax and behavior of outer joins, SQL developers can effectively handle scenarios where data from multiple tables needs to be combined while accommodating missing or unmatched rows.

Next Article ❯