Advanced SQL Techniques

Advanced MySQL SQL Tricks

This chapter covers advanced SQL techniques you'll actually use in production: recursive CTEs, LATERAL joins, upserts, multi-table DML, pivoting, JSON aggregation, and optimizer hints. Every technique includes a real business scenario with complete, runnable SQL.

1. CTEs and Recursive CTEs

1.1 Regular CTEs (readability)

WITH
  active_users AS (
    SELECT id, name FROM users WHERE status = 'active'
  ),
  user_totals AS (
    SELECT user_id, SUM(amount) AS total FROM orders WHERE status = 'paid' GROUP BY user_id
  )
SELECT u.id, u.name, t.total
FROM active_users u LEFT JOIN user_totals t ON u.id = t.user_id
ORDER BY t.total DESC;

1.2 Recursive CTEs (tree structures)

-- Find all descendants of department id=1
WITH RECURSIVE dept_tree AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM departments WHERE id = 1          -- anchor

  UNION ALL

  SELECT d.id, d.name, d.parent_id, dt.depth + 1
  FROM departments d
  INNER JOIN dept_tree dt ON d.parent_id = dt.id
  WHERE dt.depth < 10                    -- prevent infinite recursion
)
SELECT * FROM dept_tree ORDER BY depth;

1.3 Date sequence generation

-- Fill date gaps in a report (January 2024)
WITH RECURSIVE dates AS (
  SELECT DATE('2024-01-01') AS d
  UNION ALL
  SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM dates WHERE d < '2024-01-31'
)
SELECT d, COALESCE(s.cnt, 0) AS sales
FROM dates LEFT JOIN (
  SELECT DATE(created_at) AS dt, COUNT(*) AS cnt FROM orders GROUP BY dt
) s ON dates.d = s.dt;

2. LATERAL Joins (MySQL 8.0+)

LATERAL allows a subquery to reference columns from preceding FROM items — perfect for "top N per group".

-- Latest 3 orders per active user
SELECT u.id, u.name, recent.order_id, recent.amount
FROM users u
JOIN LATERAL (
  SELECT order_id, amount
  FROM orders
  WHERE user_id = u.id          -- references outer u.id
  ORDER BY created_at DESC LIMIT 3
) AS recent ON TRUE
WHERE u.status = 'active';

3. Upsert: INSERT ON DUPLICATE KEY UPDATE

-- Increment page view counter (insert if new, increment if exists)
INSERT INTO page_stats (page_id, pv, last_visit)
VALUES (101, 1, NOW())
ON DUPLICATE KEY UPDATE
  pv = pv + 1,
  last_visit = NOW();

-- Bulk upsert (efficient import)
INSERT INTO product_prices (product_id, price, updated_at)
VALUES (1, 99.9, NOW()), (2, 199.9, NOW())
ON DUPLICATE KEY UPDATE
  price = VALUES(price),          -- MySQL 8.0.20+ use alias syntax: new.price
  updated_at = VALUES(updated_at);

4. Multi-table UPDATE and DELETE

-- UPDATE users via JOIN with orders
UPDATE users u
JOIN orders o ON u.id = o.user_id
SET u.last_order_at = o.created_at
WHERE o.status = 'paid' AND o.id = 9999;

-- DELETE users with no orders
DELETE u
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.user_id IS NULL AND u.created_at < '2023-01-01';

5. Pivot (Rows to Columns)

-- Static pivot: monthly sales per product
SELECT
  product_id,
  SUM(CASE WHEN month = 1 THEN sales ELSE 0 END) AS jan,
  SUM(CASE WHEN month = 2 THEN sales ELSE 0 END) AS feb,
  SUM(CASE WHEN month = 3 THEN sales ELSE 0 END) AS mar
FROM monthly_sales
GROUP BY product_id;

6. JSON Aggregation

-- Aggregate child rows as JSON array on parent
SELECT
  u.id, u.name,
  JSON_ARRAYAGG(
    JSON_OBJECT('id', o.id, 'amount', o.amount)
  ) AS orders
FROM users u LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Key-value map per product
SELECT product_id,
  JSON_OBJECTAGG(attr_name, attr_value) AS attributes
FROM product_attributes GROUP BY product_id;

7. Optimizer Hints (MySQL 8.0+)

-- Force index
SELECT /*+ INDEX(orders idx_user_id) */ * FROM orders WHERE user_id = 1;

-- Force hash join
SELECT /*+ HASH_JOIN(u, o) */ u.name, o.amount
FROM users u JOIN orders o ON u.id = o.user_id;

-- Query timeout (kill after 5 seconds)
SELECT /*+ MAX_EXECUTION_TIME(5000) */ * FROM large_table WHERE ...;