Learn about SQL FULL OUTER JOIN with the help of examples. Learn how to use the SQL FULL OUTER JOIN to combine data from two tables, returning all rows from both tables, even if there are no matching rows in the other table.
The SQL FULL OUTER JOIN
statement joins two tables based on a common column. It selects records that have matching values in these columns and the remaining rows from both of the tables.
-- 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.
The syntax of the SQL FULL OUTER JOIN
statement is:
SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column1 = table2.column2;
Here,
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).
Example: SQL FULL OUTER JOIN
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.
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.
#sql