SQL join and NULL interview traps, answered and tested
In this article (5 sections)
Related programme: the Data Analytics with Generative AI course (3–4 months; ₹90,000 (inclusive of GST) · EMI from ₹4,000/month).
Most SQL join questions in analyst interviews are really NULL questions: a LEFT JOIN that quietly becomes an INNER JOIN, a NOT IN that returns nothing, a COUNT that disagrees with a SUM. Interviewers use them because the syntax is easy and the wrong answer looks right. Below are nine such traps, each with the answer that sounds fine, the answer that holds up, and a runnable query so you can see the gap yourself.
The traps at a glance
| Question | Weak answer | Strong answer |
|---|---|---|
1. LEFT JOIN customers to orders, then filter o.status = 'paid' in WHERE. How many customers? | "All customers, it's a LEFT JOIN." | Only customers with a paid order. The WHERE test is false or unknown for NULL rows, so the unmatched customers are removed. Move the condition into ON. |
2. WHERE id NOT IN (SELECT customer_id FROM orders) returns zero rows. Why? | "There are no such customers." | One customer_id in the subquery is NULL. x NOT IN (1, NULL) is unknown, never true. Use NOT EXISTS or filter NULLs out. |
3. COUNT(*) vs COUNT(col) after a LEFT JOIN. | "Same thing." | COUNT(*) counts rows including the NULL-extended ones; COUNT(o.order_id) counts matched rows only. For "orders per customer", count the order key. |
| 4. Revenue doubled after joining orders to payments. | "Data issue on the source side." | One order has two payment rows (a split or retried payment). The join fans out. Aggregate payments per order first, then join. |
5. WHERE discount = NULL returns nothing. | "No NULL discounts." | = NULL is always unknown. Use IS NULL. |
6. AVG(discount) looks too high. | "It's the average." | AVG ignores NULLs. If NULL means "no discount", the business average needs AVG(COALESCE(discount, 0)). Ask which one they mean. |
7. Two tables joined on city, some rows fail to match. | "Missing cities." | Check case and whitespace ("Noida" vs "noida "), and whether either side has NULL keys — NULL never equals NULL in a join. |
| 8. When would you use a FULL OUTER JOIN? | "Never needed it." | Reconciling two systems: keep rows present in either, then flag only_in_crm, only_in_billing, in_both. |
9. INNER JOIN vs WHERE EXISTS for "customers who ordered". | "Same result." | Same customers, but the join returns one row per matching order unless you DISTINCT it; EXISTS returns each customer once and states intent. |
The interviewer is checking one thing throughout: whether you reason in three-valued logic (true, false, unknown) and think about row counts before and after every join.
Prove traps 1 to 3 with one small dataset
Synthetic data: four customers, one of whom has never ordered, and an orders table with a row that has lost its customer id.
CREATE TABLE customers (id INTEGER, name TEXT);
CREATE TABLE orders (order_id INTEGER, customer_id INTEGER, status TEXT, amount INTEGER);
INSERT INTO customers VALUES (1,'Anil'),(2,'Bhavna'),(3,'Chetan'),(4,'Deepa');
INSERT INTO orders VALUES
(101,1,'paid',1200),(102,1,'refunded',800),(103,2,'paid',450),
(104,3,'pending',999),(105,NULL,'paid',300);
-- Trap 1, weak: filter in WHERE (returns 2 customers)
SELECT c.name, o.status FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid';
-- Trap 1, strong: filter in ON (returns all 4, NULL where no paid order)
SELECT c.name, o.order_id FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'
ORDER BY c.id;
-- Trap 2, weak: NOT IN with a NULL in the list (returns 0 rows)
SELECT name FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);
-- Trap 2, strong: NOT EXISTS (returns Deepa)
SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
-- Trap 3: COUNT(*) says Deepa has 1 order; COUNT(order_id) says 0
SELECT c.name, COUNT(*) AS count_star, COUNT(o.order_id) AS count_orders
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name ORDER BY c.id;A good spoken answer to trap 2 also mentions order 105: an order with a NULL customer id is itself a data-quality finding. Before writing NOT EXISTS, say you would report how many orders are orphaned, because that number belongs in the analysis, not just in the fix.
Prove trap 4, the fan-out
CREATE TABLE orders (order_id INTEGER, amount INTEGER);
CREATE TABLE payments (order_id INTEGER, paid INTEGER);
INSERT INTO orders VALUES (1,1000),(2,2000);
INSERT INTO payments VALUES (1,500),(1,500),(2,2000);
-- Weak: order revenue summed after the join (4000, wrong)
SELECT SUM(o.amount) AS order_revenue FROM orders o JOIN payments p ON p.order_id = o.order_id;
-- Strong: collapse payments to one row per order first
SELECT SUM(o.amount) AS order_revenue, SUM(p.paid) AS collected
FROM orders o
JOIN (SELECT order_id, SUM(paid) AS paid FROM payments GROUP BY order_id) p
ON p.order_id = o.order_id;The weak query reports ₹4,000 of order value when the true figure is ₹3,000. In an interview, describe the check you run every time: count rows in the left table, count rows after the join, and if the second is larger, find the key that is not unique on the right side.
Prove trap 8, a reconciliation join
CREATE TABLE crm (lead_phone TEXT);
CREATE TABLE billing (payer_phone TEXT);
INSERT INTO crm VALUES ('9811111111'),('9822222222'),('9833333333');
INSERT INTO billing VALUES ('9822222222'),('9833333333'),('9844444444');
SELECT COALESCE(c.lead_phone, b.payer_phone) AS phone,
CASE WHEN c.lead_phone IS NULL THEN 'only_in_billing'
WHEN b.payer_phone IS NULL THEN 'only_in_crm'
ELSE 'in_both' END AS status
FROM crm c FULL OUTER JOIN billing b ON b.payer_phone = c.lead_phone
ORDER BY phone;SQLite added RIGHT and FULL OUTER JOIN in version 3.39. If you are asked to do this on an older engine or MySQL, the standard workaround is a LEFT JOIN in each direction combined with UNION — worth saying, because it shows you know why the feature exists and not just its name.
Follow-up questions to expect
- "How would you find duplicate keys before joining?" —
GROUP BY key HAVING COUNT(*) > 1on each side. - "What does
COALESCEdo to a join key?" — it can create false matches (turning NULLs into'unknown'makes all unknowns match each other). Only coalesce keys when that is the intended rule. - "Does
DISTINCTfix a fan-out?" — it hides it. It can also merge genuinely different rows that happen to share values. Fix the grain instead. - "What is the grain of this result?" — say it in words: "one row per customer per paid order". If you cannot say it, the query is not finished.
For the next step, the window function question bank builds on these same tables. Joins across messy CRM and billing extracts are a regular exercise in the Data Analyst programme, where reconciliation is taught as a habit rather than a trick.
Pankit Kumar has 10 years in Data Science & AI, building and shipping production systems in regulated pharma and clinical environments. He is a freelance trainer at Boston Institute of Analytics, AnalytixLabs and Scaler, and has taught this material to thousands of working professionals.
This article is part of our Data Analytics with Generative AI programme — 3–4 months. The full analyst stack — Excel, SQL, Power BI and Python pipelines — then a generative-AI layer you can prove is right.
Explore Data Analytics with Generative AI