Tickets with their assignee
SELECT t.id, t.title, u.name AS assignee_name
FROM tickets t
INNER JOIN users u ON t.assignee_id = u.id;Unassigned tickets (assignee_id IS NULL) do not appear in this result at all.
Back to week 2
Topic
Joins
Definition
A join combines rows from two or more tables by matching values in a related column, most commonly a foreign key column against the primary key it references.
In simpler words
A join answers “show me this row together with the related row from another table” — like a ticket alongside the user assigned to it.
This app’s core relationship — a ticket and its assignee — lives across two tables. A join is how you see both together in one query.
After this you can
- Write an INNER JOIN between tickets and users
- Explain when a LEFT JOIN keeps rows an INNER JOIN would drop
- Aggregate across a join with GROUP BY and COUNT
INNER JOIN keeps only matches
Definition
An INNER JOIN returns only the rows from both tables where the join condition is satisfied, discarding rows on either side that have no match.
In simpler words
If a ticket has no assignee, an INNER JOIN on assignee_id = users.id simply leaves that ticket out of the result.
The join condition mirrors the foreign key: tickets.assignee_id = users.id.
Column names can collide (both tables might have "id"), so alias tables and select explicit columns.
LEFT JOIN keeps unmatched rows too
Definition
A LEFT JOIN returns every row from the left table, filling in null for columns from the right table when no matching row exists.
In simpler words
Use LEFT JOIN when the “left” rows matter on their own, whether or not they have a match — unassigned tickets should still show up.
Same join condition as INNER JOIN, but rows with assignee_id IS NULL still appear, with assignee columns as null.
This is usually the correct default for “list all tickets” screens, since an unassigned ticket is still a real ticket.
All tickets, assignee optional
SELECT t.id, t.title, u.name AS assignee_name
FROM tickets t
LEFT JOIN users u ON t.assignee_id = u.id;assignee_name is NULL for tickets with no assignee_id, but the ticket row is still there.
Aggregating across a join
Definition
Aggregate functions such as COUNT combine multiple rows into a single summary value per group, and GROUP BY defines which rows belong to the same group.
In simpler words
Joining first, then grouping, answers questions like “how many tickets does each user have” in one query instead of one query per user.
GROUP BY u.id groups all matching ticket rows per user before COUNT(*) counts them.
Every selected column that is not aggregated must appear in GROUP BY, or the query is ambiguous.
Ticket count per user
SELECT u.id, u.name, COUNT(t.id) AS ticket_count
FROM users u
LEFT JOIN tickets t ON t.assignee_id = u.id
GROUP BY u.id, u.name;LEFT JOIN here keeps users with zero tickets in the result, with ticket_count = 0.
Keep in mind
- Pick INNER vs LEFT by asking: should unmatched rows disappear, or stay with nulls?
- Always alias joined tables so ambiguous columns like id are unambiguous in SELECT.
- A join condition that is not the actual foreign key relationship usually signals a modeling mistake.
Test
Check your understanding
At least 10 questions — mix of concept, syntax, practical, and logic. Score ≥ 80% (enforced by the API) to save progress.
Checking your session…
10 questions · concept 3 · syntax 2 · practical 3 · logic 2