Concept 1 of 61 min
Joins and how many rows they return
Counting joined rows
| Clients | 4 |
|---|---|
| Orders, client 1 | 3 |
| Orders, client 2 | 2 |
- INNER JOIN = 3 + 2 = 5 rows
- LEFT JOIN = 5 + 2 = 7 rows
The LEFT JOIN keeps clients 3 and 4, with NULLs.
A CROSS JOIN
| Clients | 4 |
|---|---|
| Other table | 6 rows |
- Rows = 4 × 6 = 24
A CROSS JOIN pairs every row with every row.
A sum inflated by a join
| Credit limit | 500 |
|---|---|
| Trades | 3 |
- SUM after join = 3 × 500 = 1,500
The limit is counted once per trade, so aggregate before joining.
A CROSS JOIN returns every pair, and a condition on the right table in WHERE turns a LEFT JOIN into an inner one.
A join to a table with several rows per key repeats the left rows, and UNION removes duplicates while UNION ALL keeps them. Count the rows of a join before running it and most bugs show up.
Unmatched clients get NULL in the right table's columns, so WHERE t.status = 'open' drops them: put the condition in the ON clause to keep every client. Summing a client column after joining to trades counts it once per trade: aggregate before joining.
Removing duplicates needs a sort or a hash, so UNION ALL is cheaper when duplicates are impossible or wanted.
- Filtering the right table in WHERE after a LEFT JOIN.
- Summing a parent column after joining to child rows.
- Using UNION when UNION ALL is meant.
After a join, your table has more rows than before. What happened, and how do you fix it?
What is the difference between an inner join and a left join? When would you use each?
A report of revenue per client doubled after someone added a join to the contacts table. How would you find and fix the cause?
A complete answer covers
- Some clients have several contacts, so their rows repeat.
- Count rows per client before and after the join.
- Aggregate revenue first, then join, or join to one contact per client.
- Add a check that totals match the source.
Also in our technical interviews, on camera, with a score.
Concept 2 of 61 min
NULL: three-valued logic
The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.
See the plansSign inConcept 3 of 61 min
GROUP BY, HAVING and the order of evaluation
The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.
See the plansSign inConcept 4 of 61 min
Window functions: running totals, ranks and changes
The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.
See the plansSign inConcept 5 of 61 min
Indexes, transactions and silent traps
The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.
See the plansSign inConcept 6 of 61 min
AdvancedWhen the planner ignores your index
The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.
See the plansSign inRecap
- An inner join returns one row per matching pair, a left join adds unmatched left rows, a cross join multiplies; a WHERE on the right table turns a LEFT JOIN into an inner one.
- NULL is unknown: = NULL is never true, NOT IN with a NULL returns nothing, aggregates skip NULL and COUNT(*) does not.
- Logical order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY; WHERE filters rows, HAVING filters groups.
- Window functions keep every row: running totals, RANK, ROW_NUMBER for the top of each group, LAG for changes.
- Indexes speed reads and slow writes; a composite index starts from its leftmost column; transactions are all or nothing.
25 questions on SQL and data analysis
Timed, like the real one, drawn from a bank of 33. Every answer explained.