Joining tables
Data about one thing is often spread across tables: customers in one, orders in another. JOIN combines rows from several tables in one query. This lesson explains INNER, LEFT, RIGHT, FULL and CROSS joins with a picture of what each returns.
- INNER JOINOnly customers who have orders, with those orders
- LEFT JOINAll customers; order columns are NULL for customers with none
- RIGHT JOINAll orders, even ones without a matching customer
- FULL OUTER JOINEverything from both sides, matched where possible
- CROSS JOINEvery customer paired with every order; rarely what you want
The ON clause says which rows match, usually a foreign key equal to a primary key.
The idea in plain words
A join looks at two tables and pairs up rows that belong together according to a condition, usually Orders.CustomerId = Customers.CustomerId. The kind of join decides what happens to rows that have no partner on the other side: drop them (INNER) or keep them with NULLs (LEFT, RIGHT, FULL).
Example
Table aliases (c and o) keep queries short.
-- Customers who have ordered, with each order
SELECT c.Name, o.OrderId, o.Total
FROM Customers AS c
INNER JOIN Orders AS o ON o.CustomerId = c.CustomerId;
-- All customers, including those with no orders
SELECT c.Name, o.OrderId
FROM Customers AS c
LEFT JOIN Orders AS o ON o.CustomerId = c.CustomerId;
-- Customers who have never ordered
SELECT c.Name
FROM Customers AS c
LEFT JOIN Orders AS o ON o.CustomerId = c.CustomerId
WHERE o.OrderId IS NULL;
-- Three tables: order lines with product names
SELECT o.OrderId, p.Name, l.Quantity
FROM Orders AS o
JOIN OrderLines AS l ON l.OrderId = o.OrderId
JOIN Products AS p ON p.ProductId = l.ProductId;Common mistakes
- Forgetting the ON condition or using the wrong columns, which multiplies rows.
- Filtering the right-hand table of a LEFT JOIN in WHERE, which silently turns it into an INNER JOIN. Put that filter in the ON clause instead.
- Joining many large tables and selecting every column when only a few are needed.
- Not indexing foreign key columns used in joins.
Interview question
What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only rows that have a match in both tables. LEFT JOIN returns every row from the left table and the matching rows from the right table; when there is no match, the right table's columns are NULL. For example, an INNER JOIN of customers and orders omits customers without orders, while a LEFT JOIN includes them.
Want the full details? Read the official Microsoft documentation for this topic.