SQL FULL OUTER JOIN

In this tutorial, we'll learn about SQL FULL OUTER JOIN with the help of examples.

The SQL FULL OUTER JOIN joins two tables based on a common column, and selects records that have matching values in these columns and remaining rows from both of the tables.

Example

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

Here's how this code works:

How to use FULL OUTER JOIN in SQL
Example: SQL FULL OUTER JOIN

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

And, the result set will contain those rows where there is a match between customer_id (of the Customers table) and customer (of the Orders table) along with all the remaining rows from both of the tables.


Syntax of FULL OUTER JOIN

The syntax of FULL OUTER JOIN is:

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

FULL OUTER JOIN With WHERE Clause

The SQL command can have an optional WHERE clause with the FULL OUTER JOIN statement. 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 snippet short and clean. For example,

SELECT C.cat_name, P.prod_title
FROM Category AS C
FULL OUTER JOIN Products AS P
ON C.cat_id= P.cat_id;

Here, the SQL command selects common rows between Category and Products table.


Full Outer Join Vs Other Joins

FULL OUTER JOIN Vs FULL JOIN

We can also use FULL JOIN instead of FULL OUTER JOIN. Basically, these two clauses are the same.

That means,

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

is similar to

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

The FULL OUTER JOIN selects the common rows as well as all the remaining rows from both of the tables. Whereas the INNER JOIN selects only the common rows between two tables.

Let's take a look at example,

FULL OUTER JOIN

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

Output

How to use FULL OUTER JOIN in SQL
Example: SQL FULL OUTER JOIN Output

INNER JOIN

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

Output

How to use INNER JOIN in SQL
Example: SQL INNER JOIN Output
FULL OUTER JOIN Vs LEFT JOIN

The FULL OUTER JOIN selects the common rows as well as all the remaining rows from both of the tables. Whereas, the LEFT JOIN selects the common rows as well as all the remaining rows from only the left table.

Let's take a look at example,

FULL OUTER JOIN

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

Output

How to use FULL OUTER JOIN in SQL
Example: SQL FULL OUTER JOIN Output

LEFT JOIN

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

Output

How to use LEFT JOIN in SQL
Example: SQL LEFT JOIN Output
FULL OUTER JOIN Vs RIGHT JOIN

The FULL OUTER JOIN selects the common rows as well as all the remaining rows from both of the tables. Whereas the RIGHT JOIN selects the common rows as well as all the remaining rows from the right table.

Let's take a look at example,

FULL OUTER JOIN

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

Output

How to use FULL OUTER JOIN in SQL
Example: SQL FULL OUTER JOIN Output

RIGHT JOIN

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

Output

How to use RIGHT JOIN in SQL
Example: SQL RIGHT JOIN Output

Recommended Readings

Did you find this article helpful?