Unite Your Data with T-SQL's FULL JOIN: No Match Left Behind!

A FULL JOIN in T-SQL returns all the rows when there is a match in either the left (first) table or the right (second) table. If there is no match in either table, it still includes the rows with NULL values for columns from the table where no match was found.

Sample Data to Demo Full Join

You can use this DB Fiddle to follow along and practice full join

Let's explain the FULL JOIN in T-SQL with an example, along with the necessary create and insert scripts to set up the tables.

Suppose we have two tables: Customers and Orders. The Customers table contains information about customers, and the Orders table contains information about orders placed by those customers.

Table: Customers

CustomerID	CustomerName	ContactEmail
1	John Doe	john.doe@example.com
2	Jane Smith	jane.smith@example.com
3	Michael Johnson	michael.johnson@example.com
4	Sarah Williams	sarah.williams@example.com
5	Tony Chiu	tony.chiu@example.com

Table: Orders

OrderID	CustomerID	OrderDate	TotalAmount
101	1	2023-08-01	100.00
102	2	2023-08-02	50.00
103	1	2023-08-02	75.00
104	3	2023-08-03	200.00
105	9	2023-08-04	150.00

Full Join Customers and Orders Tables

Full Join Customers and Orders Tables

SELECT C.CustomerID
	,C.CustomerName
	,C.ContactEmail
	,O.OrderID
	,O.OrderDate
	,O.TotalAmount
FROM Customers C
FULL JOIN Orders O ON C.CustomerID = O.CustomerID;

CustomerID	CustomerName	ContactEmail	OrderID	OrderDate	TotalAmount
1	John Doe	john.doe@example.com	101	2023-08-01	100.00
1	John Doe	john.doe@example.com	103	2023-08-02	75.00
2	Jane Smith	jane.smith@example.com	102	2023-08-02	50.00
3	Michael Johnson	michael.johnson@example.com	104	2023-08-03	200.00
4	Sarah Williams	sarah.williams@example.com	null	null	null
5	Tony Chiu	tony.chiu@example.com	null	null	null
null	null	null	105	2023-08-04	150.00

The given SQL query is using a FULL JOIN to retrieve data from the Customers and Orders tables. Let's break down the query step by step:

1. SELECT C.CustomerID, C.CustomerName, C.ContactEmail, O.OrderID, O.OrderDate, O.TotalAmount: This part of the query specifies the columns that we want to include in the final result. It selects the CustomerID, CustomerName, and ContactEmail columns from the Customers table and the OrderID, OrderDate, and TotalAmount columns from the Orders table.

2. FROM Customers C: The FROM clause indicates the source tables for the query. Here, we are selecting data from the Customers table and aliasing it as C. The alias C is used as a shorthand reference to the Customers table throughout the query.

3. FULL JOIN Orders O ON C.CustomerID = O.CustomerID: This is the FULL JOIN operation. It combines the rows from both the Customers and Orders tables based on the condition specified after the ON keyword. In this case, the join condition is C.CustomerID = O.CustomerID, which means we are matching rows from the two tables where the CustomerID in the Customers table is equal to the CustomerID in the Orders table.

The result of the FULL JOIN includes all rows from both the Customers and Orders tables, regardless of whether there is a match or not. If there is a match, the corresponding columns from both tables are displayed in the same row. If there is no match, the columns from the non-existent table will contain NULL values in the result.

The final result of the query will be a table with the following columns: CustomerID, CustomerName, ContactEmail, OrderID, OrderDate, and TotalAmount. It will contain data from both tables, showing customer information alongside order details for customers who have placed orders, and NULL values for order-related columns for customers who have not placed any orders or orders that do not have a matching customer.

Full Join to Identify Customers without Order or Orders with Invalid Customer

Full Join Customers and Orders Tables and Find Unmatched Records

SELECT C.CustomerID
	,C.CustomerName
	,C.ContactEmail
	,O.OrderID
	,O.OrderDate
	,O.TotalAmount
FROM Customers C
FULL JOIN Orders O ON C.CustomerID = O.CustomerID
WHERE C.CustomerID IS NULL -- Invalid customer
	OR O.OrderID IS NULL -- Customer without any order

CustomerID	CustomerName	ContactEmail	OrderID	OrderDate	TotalAmount
4	Sarah Williams	sarah.williams@example.com	null	null	null
5	Tony Chiu	tony.chiu@example.com	null	null	null
null	null	null	105	2023-08-04	150.00

The WHERE clause is applied after the FULL JOIN, and it filters the result set to include only those rows that meet either of the specified conditions.

The final result will be a table with the following columns: CustomerID, CustomerName, ContactEmail, OrderID, OrderDate, and TotalAmount. It will show rows where either the CustomerID is NULL (invalid customers) or the OrderID is NULL (customers without any orders). This can be helpful for identifying data inconsistencies or issues in the data set.