What is outer join?

An outer join is a type of join in SQL that returns matched values along with unmatched values from one or both of the tables being joined.

Understanding Outer Joins

Unlike an INNER JOIN which only returns rows where there is a match in both tables, an outer join ensures that all rows from at least one of the tables are included in the result set, regardless of whether there's a matching row in the other table.

Types of Outer Joins

There are primarily three types of outer joins:

• LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and the matched rows from the right table. If there is no match in the right table, it returns NULL values for the columns of the right table. According to the reference, LEFT JOIN returns "only unmatched rows from the left table, as well as matched rows in both tables."
• RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and the matched rows from the left table. If there is no match in the left table, it returns NULL values for the columns of the left table.
• FULL OUTER JOIN: Returns all rows from both tables. If there are no matches between the tables, it fills in NULL values for the missing columns.

Example

Let's consider two tables: Customers and Orders.

Customers Table:

CustomerID	CustomerName
1	John Doe
2	Jane Smith
3	David Lee

Orders Table:

OrderID	CustomerID	OrderDate
101	1	2023-01-15
102	2	2023-02-20
103	1	2023-03-10

• A LEFT JOIN from Customers to Orders would return all customers, along with their orders if they exist. David Lee would be included, even if he has no orders.

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

• A RIGHT JOIN from Customers to Orders would return all orders, along with the corresponding customer information.

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

• A FULL OUTER JOIN would return all customers and all orders, combining the results of both LEFT JOIN and RIGHT JOIN.

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

Use Cases

Outer joins are useful in several scenarios:

• Identifying missing data in related tables.
• Generating reports that include all records from one table, even if there are no corresponding records in another table.
• Performing data analysis that requires a complete view of one or more datasets.