SQL Left Join

What LEFT JOIN does

A LEFT JOIN (also written LEFT OUTER JOIN) guarantees that every row from the first, or left, table appears in the result at least once. When the database finds a matching row in the right table, it attaches that data as usual. When no match exists, it still keeps the left row but fills the right table's columns with NULL. This makes the LEFT JOIN ideal for questions like which customers have not placed any orders.

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

Here Customers is the left table, so all four customers appear. Anita Rao shows up twice because she has two orders. Chloe Dubois and Diego Alvarez have no orders, so their OrderID and Amount come back as NULL. Order 104 does not appear at all, because it belongs to the right table and has no matching customer.

Finding rows with no match

A common pattern is to LEFT JOIN and then keep only the rows where the right side came back NULL. This isolates the left-table records that have no counterpart, such as customers who have never ordered.

SELECT Customers.CustomerName
FROM Customers
LEFT JOIN Orders
  ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderID IS NULL;

• The table listed before LEFT JOIN is the left table, and all of its rows are preserved.
• Unmatched right-table columns are returned as NULL.
• Swapping the table order changes which rows are guaranteed to appear.
• LEFT JOIN is the standard tool for reporting on records that may have no related data.