⚠️ 23. Common Subquery Mistakes

Mistake 1 — Returning multiple rows with =

Use IN instead of = when multiple rows are expected.

Mistake 2 — Forgetting NULL behavior with NOT IN

Mistake 3 — Ignoring duplicates

Mistake 4 — Making the query unnecessarily complicated

🎤 SQL Interview Questions

Q1. What is a subquery?

A query nested inside another query.

Q2. What is a scalar subquery?

A subquery that returns a single value.

Q3. When should you use IN?

When the subquery returns a set of values.

Q4. What does EXISTS do?

It checks whether at least one matching row exists.

Q5. What is a correlated subquery?

A subquery that references a column from the outer query.

Q6. What is a derived table?

A subquery in the FROM clause treated as a temporary result set.

Q7. Can a subquery be used in SELECT?

Yes.

Q8. What is the difference between IN and EXISTS?

IN compares against a set, while EXISTS checks for existence.

Q9. Why is NOT IN dangerous with NULL?

Three-valued logic can cause unexpected results.

Q10. Can every subquery be replaced with a JOIN?

Many can, but the best approach depends on the logic.

📝 Practice Questions

Practice 1 — Employees earning more than average:

SELECT employee_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

Practice 2 — Customers with at least one order:

SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
);

Practice 3 — Customers with more than 5 orders:

SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING COUNT(*) > 5
);

Practice 4 — Products higher than average price:

SELECT product_name, price
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

Practice 5 — Customers who never placed an order:

SELECT c.customer_id, c.customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

🧪 Mini SQL Challenge

Find customers whose total spending is greater than the average customer spending.

SELECT customer_id, total_spending
FROM (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
) AS customer_totals
WHERE total_spending > (
    SELECT AVG(total_spending)
    FROM (
        SELECT
            customer_id,
            SUM(amount) AS total_spending
        FROM orders
        GROUP BY customer_id
    ) AS totals
);

💡 Double Tap ❤️ For More