SQL OUTER JOIN

Outer Join in SQL is a method used to merge rows from multiple tables, accommodating instances where there might not be corresponding rows in one or more of those tables. This operation ensures that every row from at least one table (referred to as the "outer" table) is represented in the output, together with any matching rows from the other table(s) involved.

Syntax

The syntax of the outer join in SQL is as follows:

We select columns from the tableA that are to be part of the outer join. Next, we mention FULL OUTER JOIN and tableB. ON defines common criteria for the join; say, one column is common in both the relations, and join is performed on it. Also, we can mention the WHERE condition if we wish to filter some records.

The outer join of two tables tableA and tableB can be represented as follows:

Demo Database

Employees Table This table will hold information on employees, including their unique ID, name, and the department they're assigned to.

Departments Table This table lists the departments within the organization, including their unique ID and name.

Types of OUTER JOIN in SQL

LEFT OUTER JOIN

In SQL, a left outer join delivers every row from the initial, or left, table, alongside corresponding rows from the subsequent, or right, table. If there exists a row in the left table without a counterpart in the right table, the resulting values for the right table's rows are defaulted to zero. These placeholders can subsequently be adjusted as needed.

Syntax

The syntax of left join is the same as full join, except there will be a LEFT keyword instead of FULL.

Left join keywords are used to perform left outer join. ON is used to identify the columns in each table to be linked.

RIGHT OUTER JOIN

The right outer join operation in SQL returns all the rows from the right, i.e., the second relation and only matching rows from the left relation. If some records from the right table don't match rows from the left table, the null value is inserted in the columns of the left table. The records that don't match in the left table are discarded.

Syntax

FULL OUTER JOIN in SQL

The full outer join operation returns all the records from both the relations, irrespective of the match from tableA or tableB.

Syntax

Here we use the FULL keyword to perform a full outer join of two tables.

OUTER JOIN SQL Example

To demonstrate the OUTER JOIN in SQL using the tables we've created (Employees and Departments), Let's illustrate how to perform a LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

LEFT OUTER JOIN Example

This query retrieves all employees and their respective department names, including those without a department.

Query:

Output:

RIGHT OUTER JOIN Example

This query shows all departments, including those without any employees, and lists any employees that are part of those departments.

Query:

Output:

FULL OUTER JOIN Example

A FULL OUTER JOIN combines the results of both LEFT JOIN and RIGHT JOIN, showing all records from both tables with matching records from either side where available.

Query:

Expected Output: