SQL Basics

SQL Functions

SQL Joins

SQL Advanced

SQL Database

SQL Tables

SQL Constraints

SQL Performance

SQL Data Types

SQL Security

SQL FULL OUTER JOIN

❮ Previous Next ❯

The SQL FULL OUTER JOIN Keyword

The FULL OUTER JOIN keyword returns all records when there is a match in either the left (table1) or the right (table2) table records. It is a combination of LEFT JOIN and RIGHT JOIN.

!FULL OUTER JOIN Diagram

Note: FULL OUTER JOIN can potentially return very large result-sets!

FULL OUTER JOIN Syntax

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

Note: FULL OUTER JOIN and FULL JOIN are the same.

Demo Database

Customers Table:

CustomerID CustomerName
1 Alfreds Futterkiste
2 Ana Trujillo Emparedados
4 Around the Horn

Orders Table:

OrderID CustomerID
10308 2
10309 99
10310 1

FULL OUTER JOIN Example

The following SQL statement selects all customers, and all orders:

FULL OUTER JOIN Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

The result set would look like this, including customers with no orders and orders with no customers:

CustomerName OrderID
Alfreds Futterkiste 10310
Ana Trujillo Emparedados 10308
Around the Horn NULL
NULL 10309

MySQL Note: MySQL does not support FULL OUTER JOIN. To achieve the same result, you must perform a UNION of a LEFT JOIN and a RIGHT JOIN.

❮ Previous Next ❯