Joins & Set Ops — Cheat Sheet

JOINs — INNER, LEFT, RIGHT, CROSS

-- Schema
-- customers: id, name, city
-- orders:    id, customer_id, total, created_at

-- INNER JOIN: only customers who have at least one order
SELECT c.name, o.id AS order_id, o.total
FROM   customers c
INNER  JOIN orders o ON c.id = o.customer_id;
-- Customers with no orders are excluded

-- LEFT JOIN: all customers, NULL for orders if none exist
SELECT c.name,
       COUNT(o.id)     AS order_count,
       SUM(o.total)    AS total_spent
FROM   customers c
LEFT   JOIN orders o ON c.id = o.customer_id
GROUP  BY c.id, c.name;
-- Returns every customer, 0 count and NULL sum for those with no orders

Subqueries

-- Scalar subquery in SELECT (executes once per output row)
SELECT
    o.id,
    o.total,
    (SELECT AVG(total) FROM orders) AS avg_order_total,
    o.total - (SELECT AVG(total) FROM orders) AS diff_from_avg
FROM orders o;

-- IN subquery: customers who placed an order this month
SELECT id, email
FROM customers
WHERE id IN (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE created_at >= DATE_FORMAT(NOW(), '%Y-%m-01')
);

-- EXISTS (often faster than IN for large sets — stops at first match)
SELECT id, email
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id          -- correlated reference
      AND o.created_at >= '2024-01-01'
);
-- Note: EXISTS uses index on orders.customer_id — check EXPLAIN

-- NOT EXISTS — customers with NO orders (better than LEFT JOIN IS NULL for large tables)
SELECT id, email
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

GROUP BY & Aggregation

-- Revenue and order count by status
SELECT
    status,
    COUNT(*)                            AS order_count,
    SUM(total)                          AS revenue,
    AVG(total)                          AS avg_order_value,
    MIN(total)                          AS min_order,
    MAX(total)                          AS max_order,
    GROUP_CONCAT(id ORDER BY id LIMIT 5) AS sample_ids
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY status
HAVING COUNT(*) >= 10          -- filter groups with at least 10 orders
ORDER BY revenue DESC;

-- Multi-column grouping: revenue by month and category
SELECT
    DATE_FORMAT(created_at, '%Y-%m') AS month,
    category,
    COUNT(*)                          AS orders,
    SUM(total)                        AS revenue
FROM orders o
JOIN products p ON o.product_id = p.id
GROUP BY month, category
ORDER BY month, revenue DESC;

-- COUNT(DISTINCT): unique customer count per day
SELECT
    DATE(created_at)            AS day,
    COUNT(DISTINCT customer_id) AS unique_customers,
    COUNT(*)                    AS total_orders
FROM orders
GROUP BY day
ORDER BY day;

HAVING Clause

-- Find customers with more than 5 orders and total spend > $1000
SELECT
    customer_id,
    COUNT(*)            AS order_count,
    SUM(total)          AS total_spent,
    AVG(total)          AS avg_order_value
FROM orders
WHERE status <> 'CANCELLED'          -- ✓ filter rows BEFORE grouping (WHERE)
GROUP BY customer_id
HAVING order_count > 5               -- ✓ filter groups AFTER aggregating (HAVING)
   AND total_spent > 1000
ORDER BY total_spent DESC;

-- Products with average rating below 3 and at least 10 reviews
SELECT
    product_id,
    AVG(rating)   AS avg_rating,
    COUNT(*)      AS review_count
FROM reviews
GROUP BY product_id
HAVING avg_rating < 3.0 AND review_count >= 10;

-- HAVING without GROUP BY — rare but valid (entire table is one group)
SELECT COUNT(*) AS total FROM orders HAVING total > 1000;

UNION & UNION ALL

-- UNION — deduplicates (sorts combined result)
SELECT id, name, 'current' AS source FROM products
UNION
SELECT id, name, 'archive' AS source FROM products_archive;
-- Removes exact duplicate rows across both queries

-- UNION ALL — no deduplication (faster)
SELECT id, name, 'UK' AS region FROM products_uk
UNION ALL
SELECT id, name, 'EU' AS region FROM products_eu
UNION ALL
SELECT id, name, 'US' AS region FROM products_us;
-- All rows returned including any duplicates

-- Performance tip:
-- UNION:     requires sort/hash to detect duplicates → O(N log N)
-- UNION ALL: simple append → O(N)
-- Always prefer UNION ALL unless duplicate removal is required

-- Column aliases — only first SELECT's aliases appear in result
SELECT id AS product_id, name AS product_name FROM products
UNION ALL
SELECT id, name FROM archived_products;  -- aliases from first SELECT are used