Learning Objective
The objective of this SQL Server tutorial is to teach you how use a FULL JOIN to return all records from the left and right tables.
What is FULL JOIN in SQL Server?
A FULL JOIN (also called FULL OUTER JOIN) is one of the 5 types of JOINS and a type of OUTER JOIN among the 3 outer JOINS available in SQL to combine columns from different related tables. In a FULL JOIN between two tables the JOIN resultset consists of all records from both the left and right tables irrespective of whether they match the JOIN condition. The JOIN condition is specified on a common column (i.e. column holding the same data even though the column names might be different in the participating tables). Rows in the left table which do not match the right table on the JOIN condition show NULL values in their columns and rows in the right table which do not match the left table on the JOIN condition show NULL values in their columns. The output of a FULL JOIN is basically a combined resultset of the LEFT and RIGHT JOIN.
A FULL JOIN can be pictorially represented as below.
Operation
A FULL JOIN can be used in all the query types i.e. SELECT, INSERT, UPDATE and DELETE. A SQL query can contain multiple FULL JOINS and a FULL JOIN can be combined with other types of JOINS like say INNER JOIN etc.
SQL Server FULL JOIN Syntax
The basic syntax of SQL Server RIGHT JOIN clause is as follows.
SELECT column_list FROM table1 FULL JOIN table2 ON table1.columnX=table2.columnX;
In this syntax,
- column_list – the list of columns from the participating tables in the SELECT statement.
- table1 – the first or left table.
- table2 – the second or right table.
- FULL OUTER JOIN/FULL JOIN – SQL keyword combination to implement a FULL JOIN (where the resultset contains all records from the both left and right tables).
- columnX – column common to both tables on which the JOIN is made.
SQL Server FULL JOIN Example
Let us see a practical example of the FULL JOIN.
Suppose we have an electronics store and the store database has 2 tables – categories and products. The categories table contains some category names to which products belong and the products table contains the names of products belonging to one or another category. The tables are represented below. We will use them as our reference for the example.
| category_id | category_name |
| 1 | Mobile |
| 2 | Headphone |
| 3 | Tablet |
| 4 | Laptop |
| 5 | Notebook |
| 6 | Phablet |
| product_id | category_id | product_name | release_date |
| 1027 | 2 | Bose Noise Cancelling Headphone 700 | 5/13/2019 |
| 1028 | 2 | Sennheiser HD 450BT | 2/4/2020 |
| 1029 | 2 | Sony WH-1000XM3 | 8/15/2018 |
| 1030 | 2 | SoundMagic ES18 | 1/1/2017 |
| 1021 | 1 | Apple iPhone 11 Pro | 9/20/2019 |
| 1022 | 1 | Samsung Galaxy Note 10 Plus | 8/23/2019 |
| 1035 | 3 | Samsung Galaxy Tab S6 | 10/11/2019 |
| 1036 | 3 | Microsoft Surface Pro | 6/15/2017 |
| 1037 | 3 | iPad Air | 3/18/2019 |
| 1038 | 3 | Lenovo Tab M8 | 8/8/2019 |
| 1039 | 3 | Dell Venue 7 | 1/4/2014 |
| 1040 | 3 | HP 7 VoiceTab | 10/23/2014 |
| 1042 | NULL | Samsung Galaxy Note 20 | NULL |
| 1045 | NULL | Microsoft Universal Foldable Keyboard | 10/29/2015 |
We will do a FULL JOIN on the above tables on the category_id column present in both tables. The below query does the same. p and c are table aliases for the products and categories tables.
SELECT * FROM products p FULL JOIN categories c ON p.category_id=c.category_id;
The query will generate the following output. As we can see the output contains all records from both the products and categories tables. That is why it has the largest number of rows (total 17 rows) in the resultset of all the JOINS.
We can also see that the products table columns have NULL values in the last 3 rows (highlighted in yellow). That is because they do not fulfil the JOIN condition with the categories table. There are no products belonging to the categories Laptop, Notebook and Phablet. Similarly, the categories table columns have NULL values in the rows 13 and 14 (highlighted in green) because they do not fulfil the JOIN condition with the products table. There are no categories for the products Samsung Galaxy Note 20 and Microsoft Universal Foldable Keyboard.
| product_id | category_id | product_name | release_date | category_id | category_name |
| 1027 | 2 | Bose Noise Cancelling Headphone 700 | 5/13/2019 | 2 | Headphone |
| 1028 | 2 | Sennheiser HD 450BT | 2/4/2020 | 2 | Headphone |
| 1029 | 2 | Sony WH-1000XM3 | 8/15/2018 | 2 | Headphone |
| 1030 | 2 | SoundMagic ES18 | 1/1/2017 | 2 | Headphone |
| 1021 | 1 | Apple iPhone 11 Pro | 9/20/2019 | 1 | Mobile |
| 1022 | 1 | Samsung Galaxy Note 10 Plus | 8/23/2019 | 1 | Mobile |
| 1035 | 3 | Samsung Galaxy Tab S6 | 10/11/2019 | 3 | Tablet |
| 1036 | 3 | Microsoft Surface Pro | 6/15/2017 | 3 | Tablet |
| 1037 | 3 | iPad Air | 3/18/2019 | 3 | Tablet |
| 1038 | 3 | Lenovo Tab M8 | 8/8/2019 | 3 | Tablet |
| 1039 | 3 | Dell Venue 7 | 1/4/2014 | 3 | Tablet |
| 1040 | 3 | HP 7 VoiceTab | 10/23/2014 | 3 | Tablet |
| 1042 | NULL | Samsung Galaxy Note 20 | NULL | NULL | NULL |
| 1045 | NULL | Microsoft Universal Foldable Keyboard | 10/29/2015 | NULL | NULL |
| NULL | NULL | NULL | NULL | 4 | Laptop |
| NULL | NULL | NULL | NULL | 5 | Notebook |
| NULL | NULL | NULL | NULL | 6 | Phablet |
We can limit the output and make it more pragmatic by specifying columns whose data value is what we actually require. The following FULL JOIN query does the same. It specifies only product_name and category_name in the SELECT column list.
SELECT product_name, category_name FROM products p FULL JOIN categories c ON p.category_id = c.category_id;
The query will generate the following output listing only the categories against products which belong to those categories.
| product_name | category_name |
| Bose Noise Cancelling Headphone 700 | Headphone |
| Sennheiser HD 450BT | Headphone |
| Sony WH-1000XM3 | Headphone |
| SoundMagic ES18 | Headphone |
| Apple iPhone 11 Pro | Mobile |
| Samsung Galaxy Note 10 Plus | Mobile |
| Samsung Galaxy Tab S6 | Tablet |
| Microsoft Surface Pro | Tablet |
| iPad Air | Tablet |
| Lenovo Tab M8 | Tablet |
| Dell Venue 7 | Tablet |
| HP 7 VoiceTab | Tablet |
| Samsung Galaxy Note 20 | NULL |
| Microsoft Universal Foldable Keyboard | NULL |
| NULL | Laptop |
| NULL | Notebook |
| NULL | Phablet |