SQL Joins and Subqueries

SELECT o.order_id, s.status
FROM orders o
JOIN shipments s
  ON s.order_id = o.order_id
 AND s.status = 'delivered';

# Add extra conditions to the join itself.

SELECT c.customer_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

# Use LEFT JOIN plus NULL filtering to find unmatched rows.

SELECT c.customer_id, x.total_spent
FROM customers c
JOIN (
  SELECT customer_id, SUM(amount) AS total_spent
  FROM orders
  GROUP BY customer_id
) x ON x.customer_id = c.customer_id;

# Pre-aggregate a table and join the result.

SELECT *
FROM products
WHERE category_id IN (
  SELECT category_id
  FROM categories
  WHERE is_active = TRUE
);

# Filter rows based on results from another query.

SELECT employee_id, salary,
       (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;

# Return a single value from a nested query.

SELECT e.employee_id, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e.department_id
);

# Reference outer query columns inside the subquery.

SELECT q.department_id, q.avg_salary
FROM (
  SELECT department_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department_id
) q;

# Use a derived table as a query source.

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

# Return rows when a related row exists.

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

# Return rows with no related matches.

SELECT email FROM customers
UNION
SELECT email FROM leads;

# Merge distinct rows from two compatible queries.

SELECT event_type FROM web_events
UNION ALL
SELECT event_type FROM mobile_events;

# Merge all rows without deduplication.

SELECT user_id FROM newsletter_subscribers
INTERSECT
SELECT user_id FROM paying_customers;

# Return rows present in both queries.

SELECT user_id FROM all_users
EXCEPT
SELECT user_id FROM banned_users;

# Return rows from the first query not present in the second.