Simple JOINs

INNER JOIN

Returns only rows with a match in both tables. Example: Person INNER JOIN Dog returns owners who have dogs and dogs with owners.

INNER JOIN syntax

SELECT p.PersonName, d.DogName FROM Person p INNER JOIN Dog d ON p.PersonID = d.OwnerID;

JOIN vs. INNER JOIN

JOIN usually means INNER JOIN. Example: FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID;

LEFT JOIN

Keeps every row from the table on the left, plus matches from the right. Missing matches show NULL.

LEFT JOIN example

SELECT p.PersonName, d.DogName FROM Person p LEFT JOIN Dog d ON p.PersonID = d.OwnerID; Includes people without dogs.

RIGHT JOIN

Keeps every row from the table on the right, plus matches from the left. Example: Person RIGHT JOIN Dog includes dogs without owners.

LEFT JOIN vs. RIGHT JOIN

A RIGHT JOIN can usually be rewritten as a LEFT JOIN by switching table order. Example: Dog LEFT JOIN Person preserves all dogs.

CROSS JOIN

Returns every possible combination of rows; it does not use ON. Example: 6 people × 7 dogs = 42 rows.

FULL OUTER JOIN

Returns all rows from both tables; unmatched values are NULL. Example: includes both a dog with no owner and a person with no dog.

IS NULL with a JOIN

Use WHERE right_table.key IS NULL to find unmatched left-table rows. Example: people with no dogs: WHERE d.OwnerID IS NULL.

One-to-many JOIN result

One person with 2 dogs appears in 2 result rows—one row for each dog. Example: Laura + Jack and Laura + Bandit.