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
Table – categories
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
Table – Products
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.
