Subqueries

Introduction

A subquery is a query nested inside another—filtering with IN, testing existence with EXISTS, or building inline tables in FROM. This chapter covers scalar, row, and table subqueries, correlated vs uncorrelated forms, and when to prefer joins.

Prerequisites

• Joins and Multi-Table Queries
• SELECT Queries Basics
• Data in users and posts

Scalar Subquery

Returns single value—one row, one column:

SELECT
  title,
  (SELECT display_name FROM users WHERE id = posts.user_id) AS author
FROM posts;

Average post count benchmark (scalar in SELECT):

SELECT
  display_name,
  (SELECT COUNT(*) FROM posts p WHERE p.user_id = u.id) AS post_count
FROM users u;

Code explanation:

• Outer query runs per row for correlated form—fine small data; watch performance large scale

Subquery in WHERE With IN

Users who published at least one post:

SELECT display_name
FROM users
WHERE id IN (
  SELECT DISTINCT user_id FROM posts WHERE published_at IS NOT NULL
);

Equivalent join:

SELECT DISTINCT u.display_name
FROM users u
INNER JOIN posts p ON p.user_id = u.id
WHERE p.published_at IS NOT NULL;

EXISTS

True if subquery returns any row:

SELECT display_name
FROM users u
WHERE EXISTS (
  SELECT 1 FROM posts p
  WHERE p.user_id = u.id AND p.published_at IS NULL
);

Find users with draft posts.

Code explanation:

• SELECT 1 inside EXISTS—value ignored; existence matters
• Often stops at first match—optimizer friendly with indexes

IN vs EXISTS

Rule of thumb: EXISTS for correlated "has related row" checks; IN for small static sets.

Row Subquery

Compares to whole row (rare):

SELECT title FROM posts
WHERE (user_id, published_at) = (
  SELECT user_id, MAX(published_at) FROM posts
);

Requires matching column count and types.

Table Subquery (Derived Table)

Subquery in FROM must have alias:

SELECT author, post_count
FROM (
  SELECT u.display_name AS author, COUNT(*) AS post_count
  FROM users u
  INNER JOIN posts p ON p.user_id = u.id
  GROUP BY u.id, u.display_name
) AS stats
WHERE post_count >= 2;

Code explanation:

• Inner query aggregates; outer filters post_count
• AS stats alias required in MySQL

Correlated vs Uncorrelated

Uncorrelated—inner runs once:

SELECT * FROM posts
WHERE user_id IN (SELECT id FROM users WHERE is_active = 1);

Correlated—inner references outer row:

SELECT u.display_name
FROM users u
WHERE EXISTS (
  SELECT 1 FROM posts p WHERE p.user_id = u.id
);

Subquery in FROM vs JOIN

Same stats example as join:

SELECT u.display_name, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id, u.display_name
HAVING post_count >= 1;

Prefer JOIN + GROUP BY when readable—optimizer may treat similarly.

Anti-Join Pattern

Users with no posts:

SELECT display_name FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM posts p WHERE p.user_id = u.id
);

Or LEFT JOIN … WHERE p.id IS NULL from joins chapter.

FAQ

Subquery in SELECT slow?

Correlated subquery per row—try join rewrite or index posts.user_id.

Must subquery return one row in WHERE =?

Scalar comparison expects one—multiple rows error; use IN or LIMIT 1 carefully.

Nested depth limit?

Deep nesting hurts readability—CTE WITH (MySQL 8+) alternative for complex SQL.

Subquery vs CTE?

WITH cte AS (…) clearer for multi-step—advanced style.

DELETE with subquery?

DELETE FROM posts WHERE user_id IN (SELECT id FROM users WHERE is_active = 0);

MySQL may need wrapped derived table for same-table delete—check version docs.

NULL and NOT IN?

NOT IN (NULL, …) yields unknown—use NOT EXISTS instead.