Sign in to save your place.

MODULE 7 OF 12

Joins

Combine data from two or more tables with INNER, LEFT, RIGHT, and FULL OUTER JOIN.

Join types

Joins combine rows from two tables based on a condition (usually matching keys).

JoinResult
INNER JOINOnly rows where both tables match
LEFT JOINAll rows from left table; matching rows from right (NULL where no match)
RIGHT JOINAll rows from right table; matching rows from left
FULL OUTER JOINAll rows from both; NULL where no match on either side

Aliases

Use aliases to shorten table names: FROM Orders o INNER JOIN Customers c ON o.CustomerId = c.Id. Then reference columns as o.OrderDate, c.Name.

Key Takeaway

INNER JOIN = only matches. LEFT JOIN = keep all left rows, add right where matched. Use ON to specify the join condition.

Venn-style

INNER = overlap only. LEFT = left circle + overlap. RIGHT = right circle + overlap. FULL OUTER = both circles.

JOIN examples

-- Orders with customer names (only orders that have a matching customer)
SELECT o.OrderId, o.OrderDate, c.Name
FROM Orders o
INNER JOIN Customers c ON o.CustomerId = c.Id;

-- All customers, with their order count (including customers with no orders)
SELECT c.Name, COUNT(o.OrderId) AS OrderCount
FROM Customers c
LEFT JOIN Orders o ON o.CustomerId = c.Id
GROUP BY c.Name;

Practice

Write: (1) INNER JOIN Orders and Products on OrderDetails. (2) LEFT JOIN Customers and Orders so every customer appears even with no orders.

Quiz

1. What does INNER JOIN return?

Only rows where both tables have a matching row
All rows from the left table
All rows from both tables
Only the first 10 rows

Correct! INNER JOIN returns only matching rows from both tables.

INNER JOIN returns only rows where the join condition matches in both tables.

2. In "FROM Orders o", what is "o"?

An alias for the Orders table
A new table
A column name
A join type

Correct! "o" is the table alias for Orders.

"o" is an alias—a short name for the Orders table used in the query.

October 2026: which rows a join keeps

INNER JOIN keeps matches. LEFT JOIN keeps every left row. A NULL join key does not match, so an inner join hides those rows. Check that before you change the join type.

Alias every table when more than one is involved. SQL Server will not invent a safe guess when two tables have a column of the same name.

SELECT c.customer_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

Try this. Left-join the practice customers to orders and count customers with no order id on the right.

Knowledge check

What happens to a NULL join key in an INNER JOIN?