OUTER

MySQL

Example

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

Output

| CustomerName  | OrderID |
| ------------- | ------- |
| Alfreds       |    7788 |
| Berge         |    9998 |
| Sanchez       |  NULL   |
| Dupont        |    1234 |

Explanation

The example depicts a LEFT JOIN (a type of OUTER JOIN) where all records from Customers table and the matched records from Orders table are returned. If there is no match, the result is NULL from the right side (Orders table), as demonstrated by the Sanchez row. The ORDER BY clause is used to sort the result-set in alphabetical order by CustomerName.

PostgreSQL

Example

SELECT Customers.customer_name, Orders.order_id
FROM Customers
LEFT JOIN Orders
ON Customers.customer_id = Orders.customer_id;

Output

| customer_name | order_id |
|---------------|----------|
| John Doe      | 01       |
| Jane Smith    | 02       |
| Alex Johnson  | null     |

Explanation

In this SQL statement, a LEFT JOIN is used to combine rows from Customers and Orders tables based on a related column between them. For each customer_id in Customers, the query retrieves a corresponding order_id from Orders. If there is no matching order_id for a customer_id, the result is null in the order_id column.

SQL Server

Example

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

Output

| OrderID | CustomerName |
|---------|--------------|
| 1       | John Doe     |
| 2       | Jane Smith   |
| 3       | Andrew White |
| 4       | NULL         |
| NULL    | Emily Green  |

Explanation

The FULL OUTER JOIN keyword returns all records when there is a match in the Orders (left) table or the Customers (right) table. The records from the Orders table are shown first. If there is no corresponding match in the Customers table, NULL is returned. The same applies for unmatched records in the Customers table when compared with the Orders table. The results are ordered by the OrderID.

Oracle

Example

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

Output

| OrderID | CustomerName |
|---------|--------------|
| 1       | John Doe     |
| 2       | Jane Doe     |
| 3       | NULL         |
| NULL    | Mike Doe     |

Explanation

In the outer join SQL command example above:

• OrderID 1, 2, 3 are matched with John and Jane Doe, hence they display together.
• There is no customer matching OrderID 3, hence OrderID is displayed with NULL for CustomerName.
• Similarly, there is no order for Mike Doe, hence the NULL value for OrderID.

SQLite

Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
LIMIT 5;

Output

CustomerName	OrderID
Alfreds	10308
Ana Trujillo	10309
Antonio	10310
Antonio	10311
Around the Horn	NULL

Explanation

In the example provided, the LEFT JOIN operation is used to join Customers and Orders tables based on the common column CustomerID. If a match is not found in the Orders table, the output is NULL. Thus, even customers who did not make any orders are included in the result set. The LIMIT 5 at the end of the query is used to restrict the output to the first five records.