“Evidently I don't understand outer joins as well as I thought I did.”
asked on Stack Exchange
Everyone can recite the difference between INNER and LEFT JOIN. Almost nobody can look at a real query with three joins and a WHERE clause and predict which rows survive.
The recitation and the prediction are different skills, and interviews (and production bugs) only ever test the second one.
'I understand JOINs, I just second-guess myself under pressure' usually isn't a confidence problem. It's a sign the mental model was never really load-bearing.
Predict before you check
SELECT c.name, o.total FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.total > 100
A customer named Priya has never placed an order. Does she appear in this result?
If you got that right, you understand something most tutorials never mention explicitly: JOIN and WHERE don't execute as one idea, they compose — and composition is where the failures hide. Getting one question right doesn't mean that composition instinct will survive a query you haven't seen. That's what Vectra actually checks for.
The gap underneath the gap
LEFT vs INNER is taught as a syntax choice, but it's really a question about set composition: which rows survive being filtered, matched, and filtered again. Learners who memorize 'LEFT keeps unmatched rows' without a working model of set membership can recite the rule and still get every non-trivial query wrong, because the rule alone doesn't tell you what a later WHERE clause does to it.