JOIN Operations

JOIN Operations

What it means

A JOIN combines rows from two or more tables based on a related column. INNER JOIN returns only rows with matches in both tables; LEFT JOIN returns all rows from the left table plus matches from the right (NULL where there’s no match); RIGHT JOIN is the mirror of LEFT; CROSS JOIN returns every combination of rows from both tables (a Cartesian product).

Examples

-- INNER JOIN: only customers WITH orders appear
SELECT c.Name, o.Amount
FROM Customers c
INNER JOIN Orders o ON c.Id = o.CustomerId;

-- LEFT JOIN: every customer appears, even those with zero orders (Amount is NULL)
SELECT c.Name, o.Amount
FROM Customers c
LEFT JOIN Orders o ON c.Id = o.CustomerId;

-- Multiple joins
SELECT c.Name, o.Amount, p.Name AS ProductName
FROM Customers c
INNER JOIN Orders o ON c.Id = o.CustomerId
INNER JOIN OrderItems oi ON o.Id = oi.OrderId
INNER JOIN Products p ON oi.ProductId = p.Id;

-- Find customers with NO orders at all
SELECT c.Name
FROM Customers c
LEFT JOIN Orders o ON c.Id = o.CustomerId
WHERE o.Id IS NULL;

Common mistake

Using INNER JOIN when you actually need LEFT JOIN — INNER JOIN silently drops rows from the left table that have no match on the right, which is easy to miss if you’re not specifically checking row counts (e.g. “customers with no orders” disappear entirely from the results).

-- Silently excludes customers who have never placed an order
SELECT c.Name, COUNT(o.Id) AS order_count
FROM Customers c
INNER JOIN Orders o ON c.Id = o.CustomerId
GROUP BY c.Name;

-- Correct: LEFT JOIN keeps every customer, showing 0 for those with no orders
SELECT c.Name, COUNT(o.Id) AS order_count
FROM Customers c
LEFT JOIN Orders o ON c.Id = o.CustomerId
GROUP BY c.Name;

Quick practice

1. What’s the key difference between INNER JOIN and LEFT JOIN?

   Answer

   INNER JOIN only returns rows with a match in both tables; LEFT JOIN returns every row from the left table, filling in NULLs for any right-table columns where there's no match.

2. How do you find rows in the left table that have no matching row in the right table?

   Answer

   Use a LEFT JOIN and then filter with WHERE right_table.key IS NULL — this catches only the rows where no match was found.

3. What does CROSS JOIN produce?

   Answer

   Every possible combination of rows from both tables (a Cartesian product) — if table A has 3 rows and table B has 4, the result has 12 rows, with no join condition involved.