SQL Subqueries: Scalar, Correlated, and Derived Tables

SQL Subqueries: Scalar, Correlated, and Derived Tables

A subquery is a SQL query nested inside another query. Subqueries can appear in SELECT, FROM, WHERE, and HAVING clauses, enabling complex data retrieval in a single statement.

Scalar Subqueries

Return a single value and can be used wherever a single value is expected:

SELECT name, salary,
  salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

Subqueries in WHERE

IN Subquery:

SELECT * FROM products
WHERE category_id IN (
  SELECT id FROM categories WHERE active = true
);

EXISTS Subquery:

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

EXISTS is often more efficient than IN for large datasets because it short-circuits once a match is found.

Correlated Subqueries

Reference columns from the outer query and execute once per outer row:

SELECT e.name, e.salary, e.department
FROM employees e
WHERE e.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department = e.department
);

This finds employees earning above their department average.

Derived Tables (FROM Subqueries)

SELECT dept_stats.department, dept_stats.avg_salary
FROM (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) AS dept_stats
WHERE dept_stats.avg_salary > 75000;

Subquery vs JOIN

Many subqueries can be rewritten as JOINs and vice versa. Generally, JOINs perform better for combining data from multiple tables, while subqueries are clearer for filtering based on aggregate conditions.

Best Practices

• Use CTEs (WITH clause) for complex or repeated subqueries to improve readability
• Prefer EXISTS over IN when checking for existence in large tables
• Avoid correlated subqueries on large tables without proper indexing
• Test subquery performance with EXPLAIN to identify potential bottlenecks

Subqueries are a powerful tool for breaking complex logic into manageable, nested components.