DML: SELECT with FULL (OUTER) JOIN

The FULL JOIN or FULL OUTER JOIN in SQL is a concept in relational database management systems (RDBMS) that allows you to combine rows from two or more tables based on a related column between them.

The FULL JOIN keyword returns all records when there is a match in either the left (table1) or the right (table2) table records. The result is NULL on either side when there is no match.

Basic Syntax

SELECT column_name(s)
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;

• table1: The left table.
• column_name(s): The column(s) you want to select.
• table2: The right table.
• table1.column_name = table2.column_name: The condition that connects the two tables.

Example 1

Let’s start with a basic example:

Table “order”

Table “customer”

We can find all orders and customers, whether they have a matching record or not, using the following query:

SELECT order.order_id, customer.name, customer.country
FROM order
FULL JOIN customer
ON order.customer_id = customer.customer_id;

The result-set will look like this:

We see that the order_id 4 has no matching customer_id in the Customers table, so it returns NULL. Similarly, the customer Sally does not have a corresponding order in the Orders table, so it also returns NULL for order_id.

Example 2 - Joining More than Two Tables

Let’s consider a new table called “product”:

Table “product”

To get all orders, customers, and products, whether they have a matching record or not, we can use a FULL JOIN twice:

SELECT order.order_id, customer.name, product.product_name
FROM ((order
FULL JOIN customer ON order.customer_id = customer.customer_id)
FULL JOIN product ON order.product_id = product.product_id);

The result-set will look like this:

| Michael | Cherry | | 4 | NULL | NULL | | NULL | Sally | NULL | | NULL | NULL | Dates |

Here, the product “Dates” doesn’t match with any order or customer, so it returns NULL in those fields. Similarly, the customer Sally and order_id 4 have no match, so they also return NULL where applicable.

Example 3 - Using FULL JOIN with WHERE Clause

You can also use the FULL JOIN clause with the WHERE clause to filter the records.

SELECT order.order_id, customer.name, customer.country
FROM order
FULL JOIN customer
ON order.customer_id = customer.customer_id
WHERE customer.country = 'USA';

Result-set:

The result only includes customers from the USA. Other customers, or customers with no matching order, are not included in the result set.

Example 4 - Using FULL JOIN with Aggregate Functions

FULL JOIN can also be used with aggregate functions like COUNT(), SUM(), AVG(), etc.

Let’s say we want to count the number of orders each customer has made:

SELECT customer.name, COUNT(order.order_id) as number_of_orders
FROM order
FULL JOIN customer
ON order.customer_id = customer.customer_id
GROUP BY customer.name;

Result-set:

This query groups the orders by customer names, and counts the number of orders each customer has made. Sally has not made any orders, so the NumberOfOrders is 0. The NULL represents the order that doesn’t have a matching customer.

1. SQL FULL OUTER JOIN Keyword - W3Schools
2. The SQL FULL OUTER JOIN syntax - SQL Tutorial
3. FULL OUTER JOIN in SQL Server - Microsoft Docs
4. SQL Aggregate Functions - W3Schools
5. The GROUP BY Statement in SQL - SQL Tutorial
6. Using JOINs in SQL Server - Microsoft Docs
7. Filtering Data with WHERE Clause in SQL Server - SQL Server Tutorial