SQL Outer Join: LEFT, RIGHT & FULL With Examples

While working with databases in SQL (Structured Query Language), we may have multiple tables in a single database. In instances where we have to retrieve data from multiple tables and combine them, the JOIN operation comes into play.

In this article, we’ll explore the OUTER JOIN operation, a specific type of JOIN operation that combines data from multiple tables and includes unmatched data from one or more joined tables. We will also see the various types of OUTER JOIN with suitable examples.

Understanding SQL OUTER JOIN And Its Types

OUTER JOIN is used to retrieve data from one or more tables by including the unmatched rows from the original tables that we want to combine.

There are three types of OUTER JOIN, they are as follows:

1. LEFT OUTER JOIN
2. RIGHT OUTER JOIN
3. FULL OUTER JOIN

We will now explore each of these types in detail with examples.

1. LEFT OUTER JOIN

This type of OUTER JOIN is commonly used when we want to retrieve unmatched tuples (rows) from only a single table, the left table in this case. Here, all the rows from the left table are displayed along with the rows from the right table that have matching rows to that of the left table. If there are no matching rows in the right table, those entries are given a NULL value by default.

Example:

Consider a table student containing the roll number and name of 3 students. This table will act as our left table.

Consider a second table marks containing the roll number and marks of two students. This table will act as our right table.

Using LEFT OUTER JOIN, we will retrieve the roll_no and name from the left table (student) and the marks of the matching rows from the right table (marks). We use the LEFT JOIN clause to perform a LEFT OUTER JOIN. The query to do this is given below.

Query:

SELECT student.roll_no, student.name, marks.marks 
FROM student 
LEFT JOIN marks ON student.roll_no = marks.roll_no;Code language: SQL (Structured Query Language) (sql)

On executing this query, all rows from student and marks column from marks table are returned.

This can be diagrammatically represented as follows:

Output:

2. RIGHT OUTER JOIN

The RIGHT OUTER JOIN works exactly the opposite of the LEFT OUTER JOIN. In this operation, all the rows from the right table are returned, along with the rows from the left table that match the ones in the right table. Missing values in the left table are given a value of NULL.

Example:

Consider the student table from the previous example. This table will act as our left table. Consider the second table marks, which acts as our right table. We have added a new tuple to the marks table containing a new roll number 104 to marks, which is not present in the student table. The updated marks table is as follows:

On performing RIGHT OUTER JOIN on these tables, we can retrieve the roll_no and marks from the right table (marks) along with the name column from the left table (student). The missing values in the left table are given a default value of NULL. We use the RIGHT JOIN clause to perform RIGHT OUTER JOIN. This can be performed using the below query.

Query:

SELECT marks.roll_no, marks.marks, student.name 
FROM student 
RIGHT JOIN marks ON student.roll_no = marks.roll_no;Code language: SQL (Structured Query Language) (sql)

On executing the above query, we obtain all rows from marks and matching rows from student.

This can be diagrammatically represented as follows:

Output:

3. FULL OUTER JOIN

This type of OUTER JOIN can be used when we want to retrieve unmatched data from both tables. In the case of this operation, all rows from the left table and right table are returned. The missing values from both tables are given a default value of NULL.

Example:

Let us consider both our tables from the previous examples. Here student will act as the left table and marks will act as the right table. All the rows from both tables are returned. This can be done using the FULL JOIN clause using the below query.

Query:

SELECT marks.marks, student.name
FROM student
FULL JOIN marks ON student.roll_no = marks.roll_no;
Code language: SQL (Structured Query Language) (sql)

Note: MySQL does not support the FULL JOIN clause however, we can get the same result by using the UNION operation on both the LEFT and RIGHT OUTER JOIN.

Query (for MySQL):

SELECT student.name, marks.marks
FROM student
LEFT JOIN marks ON student.roll_no = marks.roll_no
UNION
SELECT student.name, marks.marks
FROM student
RIGHT JOIN marks ON student.roll_no = marks.roll_no;
Code language: SQL (Structured Query Language) (sql)

On executing the above query all rows from both student and marks are returned.

This can diagrammatically be represented as follows:

Output:

Conclusion

Understanding what Outer Joins are and how we can use them to retrieve data is crucial to know while working with multiple tables in SQL. In this article, we have seen what an OUTER JOIN is and the three different types of Outer Joins – LEFT, RIGHT and FULL OUTER JOIN, along with suitable examples and diagrammatic representation. Each of these Outer Joins performs a unique operation and can be useful for displaying matching data from two or more different tables in a database.

Reference

https://dev.mysql.com/doc/refman/8.0/en/outer-join-simplification.html