Subqueries & Advanced Filtering

Real-World Example: E-commerce Analytics

-- Products priced above average
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Orders larger than average order value
SELECT order_id, customer_id, order_total
FROM orders
WHERE order_total > (SELECT AVG(order_total) FROM orders)
ORDER BY order_total DESC;

-- Customers who spent more than average
SELECT customer_id, name, total_spent
FROM customers
WHERE total_spent > (SELECT AVG(total_spent) FROM customers);

Real-World Example: Customer Segmentation

-- Customers who made purchases this month
SELECT customer_id, name, email
FROM customers
WHERE customer_id IN (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date >= DATE('now', 'start of month')
);

-- Products that were never ordered
SELECT product_id, product_name, price
FROM products
WHERE product_id NOT IN (
    SELECT DISTINCT product_id
    FROM order_items
    WHERE product_id IS NOT NULL
);

-- High-value customers (lifetime value > $1000)
SELECT name, email
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING SUM(order_total) > 1000
);

Equivalent Queries:

-- Greater than ANY
WHERE salary > ANY (...)

-- Greater than ALL
WHERE salary > ALL (...)

-- Same as > ANY
WHERE salary > (SELECT MIN(salary) ...)

-- Same as > ALL
WHERE salary > (SELECT MAX(salary) ...)

MIN/MAX is usually clearer and more commonly used!

Performance Warning!

Correlated subqueries can be slow because they execute many times:

-- SLOW: Runs subquery for each row (1000 rows = 1000 executions)
SELECT name, salary
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

-- FASTER: Join to pre-calculated averages (2 queries instead of 1000)
SELECT e.name, e.salary
FROM employees e
JOIN (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) dept_avg ON e.department_id = dept_avg.department_id
WHERE e.salary > dept_avg.avg_salary;

Use JOINs when possible for better performance!

Why SELECT 1 in EXISTS?

You'll often see SELECT 1 in EXISTS subqueries:

WHERE EXISTS (
    SELECT 1  -- Could be *, NULL, or any value
    FROM table
    WHERE condition
)

EXISTS only checks if rows exist, not what data they contain. SELECT 1 is a convention meaning "we don't care about the actual values."

Real-World Example: Customer Activity

-- Active customers (made at least one purchase this year)
SELECT customer_id, name, email
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
    AND STRFTIME('%Y', o.order_date) = '2024'
);

-- Products with reviews
SELECT product_id, product_name, price
FROM products p
WHERE EXISTS (
    SELECT 1
    FROM reviews r
    WHERE r.product_id = p.product_id
);

-- Customers who bought premium products
SELECT name, email
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    WHERE o.customer_id = c.customer_id
    AND p.category = 'Premium'
);

NOT EXISTS vs NOT IN

WHERE NOT EXISTS (
    SELECT 1
    FROM table2
    WHERE table2.id = table1.id
)

WHERE id NOT IN (
    SELECT id
    FROM table2
    WHERE id IS NOT NULL
)

Recommendation: Prefer NOT EXISTS over NOT IN for better NULL handling and performance!

Real-World Example: Finding Gaps

-- Inactive customers (no orders in the last 6 months)
SELECT customer_id, name, email, last_login
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
    AND o.order_date >= DATE('now', '-6 months')
);

-- Products never purchased
SELECT product_id, product_name, price, stock
FROM products p
WHERE NOT EXISTS (
    SELECT 1
    FROM order_items oi
    WHERE oi.product_id = p.product_id
);

-- Employees who never attended training
SELECT employee_id, name, hire_date
FROM employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM training_attendance t
    WHERE t.employee_id = e.employee_id
)
AND hire_date < DATE('now', '-90 days');

Real-World Example: Sales Analysis

-- Monthly sales with year-over-year comparison
SELECT
    current.month,
    current.revenue AS current_year_revenue,
    previous.revenue AS previous_year_revenue,
    ROUND(100.0 * (current.revenue - previous.revenue) / previous.revenue, 2) AS growth_pct
FROM (
    SELECT
        STRFTIME('%m', order_date) AS month,
        SUM(order_total) AS revenue
    FROM orders
    WHERE STRFTIME('%Y', order_date) = '2024'
    GROUP BY STRFTIME('%m', order_date)
) AS current
LEFT JOIN (
    SELECT
        STRFTIME('%m', order_date) AS month,
        SUM(order_total) AS revenue
    FROM orders
    WHERE STRFTIME('%Y', order_date) = '2023'
    GROUP BY STRFTIME('%m', order_date)
) AS previous ON current.month = previous.month
ORDER BY current.month;

Real-World Example: Customer Dashboard

-- Customer summary with related calculations
SELECT
    c.customer_id,
    c.name,
    c.email,
    (SELECT COUNT(*)
     FROM orders o
     WHERE o.customer_id = c.customer_id) AS order_count,
    (SELECT COALESCE(SUM(order_total), 0)
     FROM orders o
     WHERE o.customer_id = c.customer_id) AS lifetime_value,
    (SELECT MAX(order_date)
     FROM orders o
     WHERE o.customer_id = c.customer_id) AS last_order_date,
    (SELECT AVG(order_total)
     FROM orders o
     WHERE o.customer_id = c.customer_id) AS avg_order_value,
    CASE
        WHEN (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) > 10
        THEN 'VIP'
        WHEN (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) > 5
        THEN 'Regular'
        ELSE 'New'
    END AS customer_tier
FROM customers c
ORDER BY lifetime_value DESC
LIMIT 20;

Optimization Examples

SELECT name, salary
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.dept = e1.dept
);

SELECT e.name, e.salary
FROM employees e
JOIN (
    SELECT dept, AVG(salary) AS avg
    FROM employees
    GROUP BY dept
) d ON e.dept = d.dept
WHERE e.salary > d.avg;