OUTER JOIN

SQL OUTER JOIN: Comprehensive Explanation

An OUTER JOIN retrieves matching rows from two tables and also includes non-matching rows from one or both tables, depending on the type of OUTER JOIN used. It is useful for finding data that exists in one table but not the other.

There are three types of OUTER JOINs:

LEFT OUTER JOIN (or simply LEFT JOIN): Includes all rows from the left table and matching rows from the right table. If no match is found in the right table, NULL is returned for those columns.
RIGHT OUTER JOIN (or simply RIGHT JOIN): Includes all rows from the right table and matching rows from the left table. If no match is found in the left table, NULL is returned for those columns.
FULL OUTER JOIN: Combines the results of LEFT JOIN and RIGHT JOIN. Includes all rows from both tables, with NULL for non-matching rows.

Syntax

LEFT JOIN:

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

RIGHT JOIN:

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

FULL OUTER JOIN:

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

Example Scenario

We will use two tables:

1. Customers Table:

CustomerID	Name	Country
1	Alice	USA
2	Bob	Canada
3	Charlie	UK
4	David	Germany

2. Orders Table:

OrderID	CustomerID	Product
101	1	Laptop
102	2	Tablet
103	1	Smartphone
104	5	Monitor

1. LEFT OUTER JOIN

Query to retrieve all customers and their orders, even if some customers have not placed any orders:

SELECT Customers.CustomerID, Customers.Name, Orders.Product
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Result:

CustomerID	Name	Product
1	Alice	Laptop
1	Alice	Smartphone
2	Bob	Tablet
3	Charlie	NULL
4	David	NULL

Explanation:

Rows for Charlie (CustomerID = 3) and David (CustomerID = 4) are included even though they have no matching orders. NULL is used for the Product column where there is no match.

2. RIGHT OUTER JOIN

Query to retrieve all orders and their associated customers, even if some orders were placed by non-existent customers:

SELECT Orders.OrderID, Orders.Product, Customers.Name
FROM Orders
RIGHT JOIN Customers
ON Customers.CustomerID = Orders.CustomerID;

Result:

OrderID	Product	Name
101	Laptop	Alice
103	Smartphone	Alice
102	Tablet	Bob
NULL	NULL	Charlie
NULL	NULL	David

Explanation:

Orders placed by non-existent customers (like OrderID = 104 with CustomerID = 5) are excluded because the right table (Customers) takes precedence.
Customers without orders (like Charlie and David) are included, with NULL for OrderID and Product.

3. FULL OUTER JOIN

Query to retrieve all customers and orders, including unmatched rows from both tables:

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

Result:

CustomerID	Name	OrderID	Product
1	Alice	101	Laptop
1	Alice	103	Smartphone
2	Bob	102	Tablet
3	Charlie	NULL	NULL
4	David	NULL	NULL
NULL	NULL	104	Monitor

Explanation:

Combines the results of LEFT JOIN and RIGHT JOIN.
Includes all customers and all orders, even if there is no match.
NULL is used for missing values.

Comparison of Outer Joins

Join Type	Included Rows
LEFT JOIN	All rows from the left table, plus matching rows from the right table. Non-matching right rows are `NULL`.
RIGHT JOIN	All rows from the right table, plus matching rows from the left table. Non-matching left rows are `NULL`.
FULL JOIN	All rows from both tables, with `NULL` for non-matching rows on either side.

Key Points

Performance Considerations:
- Outer joins can be slower than inner joins because they process additional rows (non-matching ones).
Use Cases:
- LEFT JOIN: Retrieve all data from one table, regardless of matches in the other.
- RIGHT JOIN: Useful when you want all data from the second table regardless of matches.
- FULL JOIN: When you need a complete view of matching and non-matching rows from both tables.

Last modified: 29 December 2024