SQL Fundamentals Under Pressure

In data science interviews, you'll write SQL on a whiteboard, shared doc, or online IDE. Mistakes are costly. Master these fundamentals so they become automatic.

Query Execution Order

The order SQL executes differs from how you write it:

-- Writing order
SELECT columns
FROM table
WHERE condition
GROUP BY column
HAVING aggregate_condition
ORDER BY column

-- Execution order (memorize this!)
1. FROM      -- Which table(s)?
2. WHERE     -- Filter rows
3. GROUP BY  -- Create groups
4. HAVING    -- Filter groups
5. SELECT    -- Choose columns
6. ORDER BY  -- Sort results

Why this matters: You can't use a SELECT alias in WHERE (it hasn't been created yet), but you CAN use it in ORDER BY.

JOIN Types Cheat Sheet

JOIN Type	Returns	Use When
INNER JOIN	Only matching rows	Need data from both tables
LEFT JOIN	All left + matching right	Keep all primary records
RIGHT JOIN	All right + matching left	Rarely used (rewrite as LEFT)
FULL OUTER	All rows from both	Need complete picture
CROSS JOIN	Cartesian product	Generate combinations

Interview pattern: "Get all users and their orders, including users with no orders"

-- Correct: LEFT JOIN preserves all users
SELECT u.user_id, u.name, o.order_id, o.amount
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id;

-- Common mistake: INNER JOIN loses users with no orders
SELECT u.user_id, u.name, o.order_id, o.amount
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id;

GROUP BY and HAVING

GROUP BY creates aggregated rows. HAVING filters those groups (WHERE filters before grouping).

-- Find customers who spent more than $1000 total
SELECT
    customer_id,
    COUNT(*) as order_count,
    SUM(amount) as total_spent
FROM orders
WHERE status = 'completed'    -- Filter before grouping
GROUP BY customer_id
HAVING SUM(amount) > 1000     -- Filter after grouping
ORDER BY total_spent DESC;

Common interview mistake: Using WHERE instead of HAVING for aggregate conditions.

NULL Handling

NULLs are interview landmines. Know these rules:

Operation	Result
NULL = NULL	NULL (not TRUE!)
NULL <> NULL	NULL (not TRUE!)
NULL + 5	NULL
COALESCE(NULL, 0)	0
COUNT(*)	Counts NULLs
COUNT(column)	Ignores NULLs
SUM(column)	Ignores NULLs

-- Find users with no orders (order_id is NULL after LEFT JOIN)
SELECT u.user_id, u.name
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.order_id IS NULL;  -- Use IS NULL, not = NULL

Interview Quick Checks

Before submitting any SQL answer:

1. Aliases: Are all table and column aliases consistent?
2. JOINs: Did you use the right JOIN type?
3. NULLs: Could NULL values affect your results?
4. GROUP BY: Does SELECT include all non-aggregated columns in GROUP BY?
5. Edge cases: What happens with empty tables or zero values?

Speed comes from muscle memory. Practice these patterns until they're automatic, then you can focus on the harder logic during interviews. :::