Full Join in SQL - Explained with 5 Examples

Overview: In this tutorial you will learn and understand how to use the Full Join in SQL with examples.

Introduction

The FULL JOIN in SQL is a type of join that combines records from two or more tables and returns all matching records from both tables, as well as non-matching records from either one of the tables. It returns all the rows from both tables, including the unmatched ones, which are filled with NULL values.

Basic syntax of a FULL JOIN in SQL is as follows:

SELECT [columns]
FROM [table1]
FULL JOIN [table2]
ON [table1].[column] = [table2].[column];

Here, [table1] and [table2] are the names of the tables to be joined, and [column] is the name of the column(s) to be used for the join. The ON clause specifies the join condition, which determines how the records from the two tables are matched.

Example #1: Simple FULL Join example

For example, to join two tables, Customers and Orders, on the customer_id column, you could use the following SQL statement:

SELECT Customers.customer_id, Customers.name, Orders.order_id, Orders.order_date

FROM Customers

FULL JOIN Orders

ON Customers.customer_id = Orders.customer_id;

This statement would return all the records from both the Customers and Orders tables, including unmatched records, and display the customer_id, name, order_id, and order_date columns.

Example #2: FULL join in SQL using WHERE clause

Below is an example of using the FULL JOIN in SQL with a WHERE clause:

SELECT Customers.customer_id, Customers.name, Orders.order_id, Orders.order_date

FROM Customers

FULL JOIN Orders

ON Customers.customer_id = Orders.customer_id

WHERE Orders.order_date BETWEEN '2022-01-01' AND '2022-12-31';

This SQL statement joins the Customers and Orders tables on the customer_id column, and returns all matching and non-matching records from both tables, where the order_date is between ‘2022-01-01’ and ‘2022-12-31’. The WHERE clause filters the records based on the specified date range, and the SELECT statement lists the columns to be displayed in the results.

The result of this statement will be all the customer information, including their order information if it exists and is within the specified date range. If a customer has no orders within the specified date range, their information will still be displayed, but the order information will be NULL.

Example #3: FULL join in SQL with update command

The FULL JOIN in SQL can also be used with an UPDATE command to update data in multiple tables. The UPDATE command modifies existing records in a table based on specific conditions. When using a FULL JOIN with the UPDATE command, it allows you to update records in multiple tables based on a join condition.

Basic syntax of a FULL JOIN with an UPDATE command in SQL is as follows:

UPDATE [table1]

SET [table1].[column] = [table2].[column]

FROM [table1]

FULL JOIN [table2]

ON [table1].[join_column] = [table2].[join_column];

Here, [table1] and [table2] are the names of the tables to be joined, and [join_column] is the name of the column to be used for the join. The SET clause specifies the new value for the [table1].[column] based on the value in [table2].[column].

For example, to update the customer_id column in the Orders table based on the customer_id column in the Customers table, you could use the following SQL statement:

UPDATE Orders

SET Orders.customer_id = Customers.customer_id

FROM Orders

FULL JOIN Customers

ON Orders.customer_name = Customers.name;

This statement joins the Orders and Customers tables on the customer_name column, and updates the customer_id in the Orders table with the matching customer_id from the Customers table. The result of this statement will be the updated customer_id values in the Orders table based on the join with the Customers table.

Example #4: FULL join with group by clause

The FULL JOIN in SQL can also be used in combination with the GROUP BY clause to group and aggregate data from multiple tables. The GROUP BY clause groups rows with the same values into summary rows based on the specified columns.

Basic syntax of a FULL JOIN with a GROUP BY clause in SQL is as follows:

SELECT [grouped_columns], SUM([aggregate_column])

FROM [table1]

FULL JOIN [table2]

ON [table1].[join_column] = [table2].[join_column]

GROUP BY [grouped_columns];

Here, [table1] and [table2] are the names of the tables to be joined, [join_column] is the name of the column to be used for the join, [grouped_columns] is the column or columns to group by, and [aggregate_column] is the column to be aggregated.

For example, to group the total sales by customer and calculate the sum of the sales for each customer from both the Orders and Returns tables, you could use the following SQL statement:

SELECT Customers.name, SUM(Orders.total_sales + Returns.total_sales) AS Total_Sales

FROM Customers

FULL JOIN Orders

ON Customers.customer_id = Orders.customer_id

FULL JOIN Returns

ON Customers.customer_id = Returns.customer_id

GROUP BY Customers.name;

This statement joins the Customers, Orders, and Returns tables on the customer_id column, and groups the results by customer name. The SELECT statement displays the customer name and the sum of the total_sales from both the Orders and Returns tables. The result of this statement will be a summary of the total sales for each customer, including customers with no sales or returns.

Example #5: FULL join using BETWEEN clause

The FULL JOIN in SQL can also be used in combination with the BETWEEN clause to filter records based on a specified range. The BETWEEN clause is used to filter records based on a range of values within a specified column.

Basic syntax of a FULL JOIN with a BETWEEN clause as follows:

SELECT [columns]

FROM [table1]

FULL JOIN [table2]

ON [table1].[join_column] = [table2].[join_column]

WHERE [table1].[column] BETWEEN [value1] AND [value2];

Here, [table1] and [table2] are the names of the tables to be joined, [join_column] is the name of the column to be used for the join, [columns] is the column or columns to be displayed in the results, [value1] and [value2] are the start and end values for the specified range in [table1].[column].

For example, to retrieve all customers and their orders placed between January 1, 2022 and December 31, 2022, you could use the following SQL statement:

SELECT Customers.name, Orders.order_id, Orders.order_date

FROM Customers

FULL JOIN Orders

ON Customers.customer_id = Orders.customer_id

WHERE Orders.order_date BETWEEN '2022-01-01' AND '2022-12-31';

This statement joins the Customers and Orders tables on the customer_id column, and retrieves all records where the order_date is between ‘2022-01-01’ and ‘2022-12-31’. The WHERE clause filters the records based on the specified date range, and the SELECT statement lists the columns to be displayed in the results.