Bits of .NET

📊 Subqueries = Complex Data

Single queries are limited. Subqueries nest queries. Complex conditions, derived data.

📝 Subquery Examples

-- Users with orders over $100
SELECT name, email
FROM users
WHERE id IN (
    SELECT user_id
    FROM orders
    WHERE total > 100
);

-- Users with no orders
SELECT name, email
FROM users
WHERE id NOT IN (
    SELECT DISTINCT user_id
    FROM orders
);

-- Products with average order above 100
SELECT name
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

-- Customers with orders in last 30 days
SELECT name, email
FROM users
WHERE EXISTS (
    SELECT 1
    FROM orders
    WHERE user_id = users.id
    AND order_date > CURRENT_DATE - INTERVAL 30 DAY
);

🎯 Advanced Subqueries

-- Subquery in SELECT
SELECT 
    name,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) as order_count,
    (SELECT COALESCE(SUM(total), 0) FROM orders WHERE user_id = users.id) as total_spent
FROM users;

-- Subquery with multiple columns
SELECT name, email
FROM users u
WHERE (SELECT COUNT(*) FROM orders WHERE user_id = u.id) > 5
AND (SELECT AVG(total) FROM orders WHERE user_id = u.id) > 100;

-- Correlated subquery
SELECT name, salary
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
);

-- Subquery in FROM (derived table)
SELECT 
    department_id,
    avg_salary
FROM (
    SELECT 
        department_id,
        AVG(salary) as avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_stats
WHERE avg_salary > 50000;

💡 Subquery Tips

• Use EXISTS for existence checks
• Use IN for list of values
• Use scalar subqueries in SELECT
• Correlated subqueries can be slow
• Consider JOIN as alternative