SQL Server FULL JOIN

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_idcategory_name
1Mobile
2Headphone
3Tablet
4Laptop
5Notebook
6Phablet
Table – categories
product_idcategory_idproduct_namerelease_date
10272Bose Noise Cancelling Headphone 7005/13/2019
10282Sennheiser HD 450BT2/4/2020
10292Sony WH-1000XM38/15/2018
10302SoundMagic ES181/1/2017
10211Apple iPhone 11 Pro9/20/2019
10221Samsung Galaxy Note 10 Plus8/23/2019
10353Samsung Galaxy Tab S610/11/2019
10363Microsoft Surface Pro6/15/2017
10373iPad Air3/18/2019
10383Lenovo Tab M88/8/2019
10393Dell Venue 71/4/2014
10403HP 7 VoiceTab10/23/2014
1042NULLSamsung Galaxy Note 20NULL
1045NULLMicrosoft Universal Foldable Keyboard10/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_idcategory_idproduct_namerelease_datecategory_idcategory_name
10272Bose Noise Cancelling Headphone 7005/13/20192Headphone
10282Sennheiser HD 450BT2/4/20202Headphone
10292Sony WH-1000XM38/15/20182Headphone
10302SoundMagic ES181/1/20172Headphone
10211Apple iPhone 11 Pro9/20/20191Mobile
10221Samsung Galaxy Note 10 Plus8/23/20191Mobile
10353Samsung Galaxy Tab S610/11/20193Tablet
10363Microsoft Surface Pro6/15/20173Tablet
10373iPad Air3/18/20193Tablet
10383Lenovo Tab M88/8/20193Tablet
10393Dell Venue 71/4/20143Tablet
10403HP 7 VoiceTab10/23/20143Tablet
1042NULLSamsung Galaxy Note 20NULLNULLNULL
1045NULLMicrosoft Universal Foldable Keyboard10/29/2015NULLNULL
NULLNULLNULLNULL4Laptop
NULLNULLNULLNULL5Notebook
NULLNULLNULLNULL6Phablet

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_namecategory_name
Bose Noise Cancelling Headphone 700Headphone
Sennheiser HD 450BTHeadphone
Sony WH-1000XM3Headphone
SoundMagic ES18Headphone
Apple iPhone 11 ProMobile
Samsung Galaxy Note 10 PlusMobile
Samsung Galaxy Tab S6Tablet
Microsoft Surface ProTablet
iPad AirTablet
Lenovo Tab M8Tablet
Dell Venue 7Tablet
HP 7 VoiceTabTablet
Samsung Galaxy Note 20NULL
Microsoft Universal Foldable KeyboardNULL
NULLLaptop
NULLNotebook
NULLPhablet

Advertisement