SQL OUTER JOIN

What is a OUTER JOIN in SQL?

We use the normalization process to organize the data and remove the redundancy from the relational database. Normalization is a process of dividing the relation (table) into multiple small relations to eliminate data repetition and inconsistency (errors).To retrieve the original relation from normalized relations, we use the select statement in Joins.

SQL Outer Join returns all the rows of the participating tables that satisfy the join conditions and rows not satisfying the join conditions.

The outer join in SQL is denoted by the $(+)$ operator only on one side of the join condition. Outer join in SQL is of three types.

1. Left Outer Join
2. Right Outer Join
3. Full Outer Join

Syntax of OUTER JOIN in SQL

The syntax of the outer join operation 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:

Example of OUTER JOIN in SQL

Let us take an example of the Student relation and Course relation. The student table consists of the student name and id and the course table consists of the course id, course name, and students enrolled in it.

Student Table

Course Table

Now let us perform the outer join.

Output:

The above table is the result of the outer join of the Student and Course tables. We can observe that

• for the course_ID 12, as there are no entries in the Student table, its columns are null.
• for the student_ID 3008 and 3007, as there are no data in the course table, columns of the course table are null.
• for the student_id 3002, as there are two courses it appears twice.

Types of OUTER JOIN in SQL

LEFT OUTER JOIN in SQL

Left outer join operation in SQL returns all the rows from the left, i.e., first relation, and only matching rows from the right, i.e., second relation. In case some row is present in the left table that is absent in the right table, then values of rows of the right table are set to zero, which can later be modified.

Visualization of Left outer join with the help of Venn diagram. The resultant relation consists of all the rows of the left table, i.e., tableA, and only matching rows of the right table.

Syntax of LEFT OUTER JOIN

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.

LEFT OUTER JOIN Example

Let us take an example of a shopping list and a list of stores in the city to understand left outer join in SQL.

Item_list table consists of a list of items, quantities, and categories. Store_list consists of a list of stores, their id, and location. We will perform left join with item_list table as our left table as we want all the items of the list but only specific stores.

Table Item_list

Table Store_list

Output:

As in the above example, all the rows of the item_list table, i.e., left table, were included but only matching rows of store_list are seen. As for each item in item_list, an entry exists in store_list. Therefore none of the columns are assigned the NULL value.

RIGHT OUTER JOIN in SQL

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.

The Right outer join can be visualized as follows using a Venn diagram. As all the records(rows) of relation are present at the right and matching records on the left, i.e., tableA, are present in the resultant relation.

Syntax of RIGHT OUTER JOIN

The syntax of Right Outer Join is the same as Left outer join except for the LEFT keyword; we use the RIGHT keyword. Here all the right table rows are included but only matching rows of the left table are added.

RIGHT OUTER JOIN Example

Let's say we have two relations, the first for the customer and the other for the order of customers. Some customers are regular and thus have their data in the record, i.e., customer_id to uniquely identify them, customer_name, and their order_id.

The other relation, order_table, has a list of orders along with order_id, order_name, and order_status. It is possible that the customer is not registered on the customer table, but still, their order exists.

Table Customer

Table Order_table

Output:

In the above relation, all the orders are included. The details of customers are fetched if it is available in the customer table; else, the columns are kept NULL (in rows 4 and 5). As we perform a right outer join, the customers not having orders are excluded, and customer details are included if order_id matches two tables.

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.

The full outer join is shown below using a Venn diagram. As all the records of both the relations are present in the resultant table, it can be shown using the union of tableA and tableB.

Syntax of FULL OUTER JOIN

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

FULL OUTER JOIN Example

To see how full join works let us take an example of an employee table. Employee table consists of employee_id, emp_name, their designation, and city. Another table called department table contains department details such as dept_id, dept_name, and emp_id.

New employees may not be assigned their departments but are added to the employee table. Also, some employees being part of multiple departments, do not have their names in a specific department. Let us perform a full join to get details of all the employees.

Table Employee

Table Department

Output:

The above resultant relation includes all the matching rows from both tables. NULL value is added in the place where entries are not available in either of the relations (tables). For the $4^{th}$ row, we don't have an entry in the employee table; thus, the columns are assigned NULL values. Similarly, in the $6^{th}$ row, we don't have data in the department table. Unlike left or right outer joins, none of the tables are prioritized.

Why Use an OUTER JOIN in SQL?

There are two types of joins in SQL: inner join and outer join.

Inner join returns the combinations of the rows that satisfy the join conditions, and the rest are discarded. Whereas outer join returns the result containing the combinations of rows that satisfy the given condition as well as rows of the dominant table that were discarded in inner join if no matching entries were found in the subordinate table. In the case of non-matching entries in the subordinate table, the resultant table will contain NULL values in columns selected from the subordinate table.

Learn More

Learn more about join in SQL.