Introduction.

In the realm of SQL databases, the OUTER JOIN operation stands as a powerful tool for integrating data from multiple tables. But what exactly is it, and how can it elevate your data retrieval game? Let's embark on a journey to demystify the SQL OUTER JOIN, exploring its types, syntax, and real-world applications.

Understanding SQL OUTER JOIN

SQL OUTER JOIN is a way to combine information from two tables, even if the records don't match. This makes it different from INNER JOIN, and it's a flexible option for getting a wide range of data.

Please consider below tables for the real world examples.

Table 1: employee_detail

emp_id	emp_name	emp_salary
001	John Doe	60000
002	Jane Smith	55000
003	Mike Johnson	62000
004	Emily Davis	58000
005	Chris Wilson	65000

Table 2: department_detail

dept_id	dept_name	emp_id
001	HR	001
002	Marketing	003
003	Sales	005
004	IT	NULL
005	Finance	NULL

Types of SQL OUTER JOIN

Left Outer Join:

When the left outer join is employed, all records from the left table are retrieved. Additionally, matching records from the right table are included, while non-matching ones appear as NULL.

Example Query:

SELECT e.emp_id, e.emp_name, e.emp_salary, d.dept_id, d.dept_name
FROM employee_detail e
LEFT OUTER JOIN department_detail d ON e.emp_id = d.emp_id;

Output Table:

emp_id	emp_name	emp_salary	dept_id	dept_name
001	John Doe	60000	001	HR
002	Jane Smith	55000	NULL	NULL
003	Mike Johnson	62000	002	Marketing
004	Emily Davis	58000	NULL	NULL
005	Chris Wilson	65000	003	Sales

In this output, all employees from the 'employee_detail' table are included, and their corresponding department information is retrieved using the Left Outer Join. Employees without assigned departments have NULL values in the 'dept_id' and 'dept_name' columns.

Right Outer Join:

The right outer join reverses the process. It fetches all records from the right table and includes matching records from the left table, with non-matching ones marked as NULL.

Example Query:

SELECT e.emp_id, e.emp_name, e.emp_salary, d.dept_id, d.dept_name
FROM employee_detail e
RIGHT OUTER JOIN department_detail d ON e.emp_id = d.emp_id;

Output Table:

emp_id	emp_name	emp_salary	dept_id	dept_name
001	John Doe	60000	001	HR
003	Mike Johnson	62000	002	Marketing
005	Chris Wilson	65000	003	Sales
NULL	NULL	NULL	004	IT
NULL	NULL	NULL	005	Finance

In this output, all departments from the 'department_detail' table are included, and their corresponding employee information is retrieved using the Right Outer Join. Departments without assigned employees have NULL values in the 'emp_id', 'emp_name', and 'emp_salary' columns.

Full Outer Join

The FULL OUTER JOIN keyword returns all matching records from both tables whether the other table matches or not.

Example Query:

SELECT e.emp_id, e.emp_name, e.emp_salary, d.dept_id, d.dept_name
FROM employee_detail e
FULL OUTER JOIN department_detail d ON e.emp_id = d.emp_id;

Output Table:

emp_id	emp_name	emp_salary	dept_id	dept_name
001	John Doe	60000	001	HR
002	Jane Smith	55000	NULL	NULL
003	Mike Johnson	62000	002	Marketing
004	Emily Davis	58000	NULL	NULL
005	Chris Wilson	65000	003	Sales
NULL	NULL	NULL	004	IT
NULL	NULL	NULL	005	Finance

In this output, a Full Outer Join retrieves all records from both the 'employee_detail' and 'department_detail' tables. Employees without assigned departments and departments without assigned employees are included, marked with NULL values in the respective columns. This ensures a comprehensive view of both datasets.