In partnership with
Here's the test: Two tables. One question. No IDE.
TABLE A (id) TABLE B (id)
------------ ------------
1 4
1 3
NULL 4
-2 NULL
3 NULL
3 5
3 6
4 7Your challenge: Calculate the exact row count for each query. Write down your numbers. Not ranges. Not "it depends." Actual counts.
SELECT * FROM A INNER JOIN B ON A.id = B.id
SELECT * FROM A LEFT JOIN B ON A.id = B.id
SELECT * FROM A RIGHT JOIN B ON A.id = B.id
SELECT * FROM A FULL OUTER JOIN B ON A.id = B.id
SELECT * FROM A CROSS JOIN BWhy this question?
Most companies use this as an opening question for analyst interviews. It's not a trick. The data is right there. Most candidates get at least one wrong.
Here's what this actually tests:
Do you understand that joins multiply, not add?
Do you know what NULL does in join conditions?
Can you trace logic manually without an IDE?
If you can't do this on paper, you will write queries that explode from 1M rows to 10M rows. Your pipeline will crash. "I didn't realize the key wasn't unique" is not an acceptable answer for analysts.
Stop scrolling: Commit to your five numbers before reading on. A quick shoutout to our sponsor who keeps this newsletter free. It helps us keep the lights on if you check them out. (Continue Reading)
Coupon Extensions Hate Us (And You’ll Love Why)
Coupon Protection partners with DTC brands like Quince, Blueland, Vessi and more to stop coupon extensions from auto-applying unwanted codes in your checkout.
Overpaid commissions to affiliates and influencers add up fast – Take back your margin.
After months of using KeepCart, Mando says “It has paid for itself multiple times over.”
Now it’s your turn to see how much more profit you can keep.
1. Inner Join → 5 Rows
Only matching values produce rows. Here's the math:
3in A (3 times) ×3in B (1 time) = 3 rows4in A (1 time) ×4in B (2 times) = 2 rows1,-2→ no match in B → 0 rows5,6,7→ no match in A → 0 rowsNULL→ Below
Total: 5 rows
The NULL Trap : NULL = NULL does not evaluate to TRUE, it evaluates to UNKNOWN. In SQL join conditions, only TRUE produces a match. So Table A's NULL and Table B's two NULLs never join. They're invisible to INNER JOIN. This trips up experienced analysts constantly.
2. Left Join → 9 Rows
LEFT JOIN = INNER JOIN matches + unmatched rows from left table (with NULLs for B columns)
INNER JOIN matches: 5 rows
Unmatched from A:
Two
1s (no match in B)One
NULL(doesn't match B's NULLs)One
-2(no match in B)Subtotal: 4 rows
Total: 9 rows
3. Right Join → 10 Rows
RIGHT JOIN = INNER JOIN matches + unmatched rows from right table
INNER JOIN matches: 5 rows
Unmatched from B:
Two
NULLs (don't match anything)One
5, one6, one7(no match in A)Subtotal: 5 rows
Total: 10 rows
4. Full Outer Join → 14 Rows
FULL OUTER = INNER matches + unmatched from A + unmatched from B
INNER JOIN: 5 rows
Unmatched from A: 4 rows
Unmatched from B: 5 rows
Total: 14 rows
No double-counting. Matched rows appear once with values from both tables. Unmatched rows appear once with NULLs on the missing side.
5. Cross Join → 64 Rows
CROSS JOIN ignores the ON clause. Every row in A pairs with every row in B.
8 × 8 = 64 rows
This is the Cartesian product. No filtering. Just multiplication.
Score Yourself:
✅ All 5 correct: You understand cardinality. You're ready for the first round of interview.
✅ 4 correct: Probably missed the NULL trap. Now you won't.
❌ 3 or fewer: Real gap. Not fatal, but fix it before your next interview.
How to answer this in Interviews:
Don't just give numbers. Walk through your logic:
"First, I'll count distinct values and their frequencies in each table."
"For INNER JOIN, I find matching values and multiply their counts."
"NULL won't match NULL in standard ANSI SQL, so those rows won't join."
"For LEFT/RIGHT/FULL, I add unmatched rows from the appropriate side."
This shows you understand why, not just what.
That's what separates analysts from people who memorized syntax.
If you got all five correct, you're thinking at the level companies expect for analyst roles.
Browse Freshly added 100+ verified analyst roles at RealAnalystJobs.com