SQL FULL OUTER JOIN

-- full join Customers and Orders tables
-- based on their shared customer_id columns
-- Customers is the left table
-- Orders is the right table

SELECT Customers.customer_id, Customers.first_name, Orders.item
FROM Customers
FULL OUTER JOIN Orders
ON Customers.customer_id = Orders.customer_id;

Here, the SQL query performs a FULL OUTER JOIN on two tables, Customers and Orders. This means that the result set contains all the rows from both tables, including the ones that don't have common customer_id values.

FULL OUTER JOIN SYNTAX

The syntax of the SQL FULL OUTER JOIN statement is:

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column1 = table2.column2;

Here,

• table1 and table2 are the tables to be joined
• column1 and column2 are the related columns in the two tables

Example: SQL OUTER Join

SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
FULL OUTER JOIN Orders
ON Customers.customer_id = Orders.customer;

Here, the SQL command selects the customer_id and first_name columns (from the Customers table) and the amount column (from the Orders table).

The result set will contain all rows of both the tables, regardless of whether there is a match between customer_id (of the Customers table) and customer (of the Orders table).

FULL OUTER JOIN With WHERE Clause

The SQL FULL OUTER JOIN statement can have an optional WHERE clause. For example,

SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
FULL OUTER JOIN Orders
ON Customers.customer_id = Orders.customer
WHERE Orders.amount >= 500;

Here, the SQL command joins two tables and selects rows where the amount is greater than or equal to 500.

SQL FULL OUTER JOIN With AS Alias

We can use AS aliases inside FULL OUTER JOIN to make our query short and clean. For example,

-- use alias C for Categories table
-- use alias P for Products table
SELECT C.category_name, P.product_title
FROM Categories AS C
FULL OUTER JOIN Products AS P
ON C.category_id = P.cat_id;

Here, the SQL command performs a full outer join on the Categories and Products tables while assigning the aliases C and P to them, respectively.

Recommended Readings

• SQL JOIN
• SQL INNER JOIN
• SQL LEFT JOIN
• SQL RIGHT JOIN