Subqueries

Introduction

A subquery is a SELECT inside another SQL statement. They appear in FROM (derived table), SELECT (scalar), WHERE (predicate), or in EXISTS / IN.

Understanding the topic

Core concepts to understand:

• Scalar subquery: returns one value.
• Derived table: FROM (SELECT ...) t.
• Correlated subquery: references outer row — runs per row, slower.
• EXISTS / IN for membership checks.
• Often rewritable as JOINs or CTEs for clarity.

Syntax reference

Visual workflow / architecture:

SELECT name,
  (SELECT count(*) FROM orders WHERE user_id = u.id) AS orders   -- scalar
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 100
);                                                              -- correlated

Real-world use

Subqueries answer questions like 'users with at least one order over $100', 'products never bought', 'top 10 customers per region'.

Best practices

• Prefer JOINs or CTEs for readability.
• Be cautious with correlated subqueries on big tables.
• Use EXISTS rather than IN for large sets.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Correlated vs uncorrelated subquery.
• Q2. EXISTS vs IN — when to choose which.
• Q3. Rewrite a subquery as a JOIN.
• Q4. Scalar subquery — what is it?
• Q5. Performance pitfalls of subqueries.