Subquery Fundamentals

SQL Subqueries: Queries Inside Queries

The Magic Nesting Doll

Imagine you have a set of Russian nesting dolls. You open the big doll, and inside there’s a smaller doll. Open that one, and there’s an even smaller one inside!

SQL subqueries work exactly like that. You put one question (query) inside another question. The little doll answers first, then the big doll uses that answer.

What is a Subquery?

A subquery is simply a query written inside another query. Think of it like asking a helper question to answer your main question.

Real-life example:

“Who scored higher than the class average?”

To answer this, you first need to know: What IS the average?

That’s two questions! The inside question finds the average. The outside question uses it.

SELECT name, score
FROM students
WHERE score > (
  SELECT AVG(score)
  FROM students
);

The part in parentheses () is the subquery (inner query). Everything outside is the outer query (main query).

Subquery Basics and Syntax

The Golden Rules

1. Always wrap subqueries in parentheses ( )
2. Inner query runs FIRST, outer query runs SECOND
3. Subqueries can go in three places: WHERE, FROM, or SELECT

The Basic Pattern

SELECT columns
FROM table
WHERE column operator (
  SELECT column
  FROM another_table
  WHERE condition
);

graph TD
    A["Main Query Starts"] --> B["Finds Subquery"]
    B --> C["Runs Subquery First"]
    C --> D["Gets Result"]
    D --> E["Uses Result in Main Query"]
    E --> F["Returns Final Answer"]

Scalar Subqueries

One Question, One Answer

A scalar subquery returns exactly ONE value. Like asking “What’s 2 + 2?” The answer is just: 4.

Example: Find products priced above the average price.

SELECT product_name, price
FROM products
WHERE price > (
  SELECT AVG(price)
  FROM products
);

The subquery SELECT AVG(price) FROM products returns ONE number (like 50.00). That single number is used in the comparison.

Where Scalar Subqueries Shine

Key Point: If your subquery returns more than one value, it’s NOT a scalar subquery!

Multi-Row Subqueries

One Question, Many Answers

Sometimes your helper question has MULTIPLE answers. Like asking “What are all the prime numbers under 10?” Answer: 2, 3, 5, 7 (four values!).

For these, you use special keywords: IN, ANY, or ALL.

Using IN

SELECT employee_name
FROM employees
WHERE department_id IN (
  SELECT department_id
  FROM departments
  WHERE location = 'New York'
);

This finds all employees whose department is in New York. The subquery might return multiple department IDs like (1, 3, 5).

Using ANY

SELECT product_name, price
FROM products
WHERE price > ANY (
  SELECT price
  FROM products
  WHERE category = 'Electronics'
);

“Price greater than ANY electronics price” means greater than at least ONE of them.

Using ALL

SELECT product_name, price
FROM products
WHERE price > ALL (
  SELECT price
  FROM products
  WHERE category = 'Electronics'
);

“Price greater than ALL electronics prices” means greater than EVERY single one.

graph TD
    A["Multi-Row Subquery Returns"] --> B["2, 5, 8, 10"]
    B --> C{Which Operator?}
    C -->|IN| D["Match ANY in list"]
    C -->|ANY| E["Compare to at least ONE"]
    C -->|ALL| F["Compare to EVERY value"]

Subquery in WHERE Clause

The WHERE clause is the most popular home for subqueries. It’s where you filter rows based on conditions.

Pattern 1: Simple Comparison

SELECT name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
);

Find employees earning above average.

Pattern 2: Membership Test (IN)

SELECT customer_name
FROM customers
WHERE customer_id IN (
  SELECT customer_id
  FROM orders
  WHERE order_date > '2024-01-01'
);

Find customers who placed orders this year.

Pattern 3: Existence Check (EXISTS)

SELECT department_name
FROM departments d
WHERE EXISTS (
  SELECT 1
  FROM employees e
  WHERE e.department_id = d.department_id
);

Find departments that have at least one employee.

Pattern 4: NOT IN (Exclusion)

SELECT product_name
FROM products
WHERE product_id NOT IN (
  SELECT product_id
  FROM order_items
);

Find products that were never ordered.

Subquery in FROM Clause

Creating a Temporary Table

You can put a subquery in the FROM clause. It acts like a temporary table you create just for this query.

You MUST give it a name (called an alias).

SELECT dept_name, avg_salary
FROM (
  SELECT department_id,
         AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department_id
) AS dept_averages
JOIN departments d
  ON dept_averages.department_id = d.department_id;

Why Use FROM Subqueries?

1. Simplify complex calculations
2. Break problems into steps
3. Create summary data to join with

graph TD
    A["FROM Subquery"] --> B["Creates Temporary Result"]
    B --> C["Give it an Alias Name"]
    C --> D["Use Like a Real Table"]
    D --> E["Join, Filter, Select from it"]

Real Example: Top Performers

SELECT e.name, summary.total_sales
FROM employees e
JOIN (
  SELECT salesperson_id,
         SUM(amount) AS total_sales
  FROM sales
  GROUP BY salesperson_id
) AS summary
  ON e.employee_id = summary.salesperson_id
WHERE summary.total_sales > 100000;

Subquery in SELECT Clause

Adding Calculated Columns

You can put a subquery right in the SELECT list! It calculates a value for each row.

SELECT
  product_name,
  price,
  (SELECT AVG(price) FROM products) AS avg_price
FROM products;

This adds a column showing the average price next to each product.

Comparing Each Row to a Total

SELECT
  department_name,
  (SELECT COUNT(*)
   FROM employees e
   WHERE e.department_id = d.department_id
  ) AS employee_count
FROM departments d;

For each department, this counts how many employees work there.

Important Rules

Putting It All Together

The Subquery Family

Quick Decision Tree

graph TD
    A["Need a Subquery?"] --> B{How many values?}
    B -->|One value| C["Scalar Subquery"]
    B -->|Many values| D["Multi-Row with IN/ANY/ALL"]
    A --> E{Where to put it?}
    E -->|Filter rows| F["WHERE clause"]
    E -->|Create temp table| G["FROM clause"]
    E -->|Add column| H["SELECT clause"]

Your Subquery Superpowers

You now know how to:

1. Write subqueries with proper parentheses
2. Use scalar subqueries for single-value comparisons
3. Handle multiple results with IN, ANY, ALL
4. Filter in WHERE for powerful conditions
5. Build temp tables in FROM for complex logic
6. Add calculated columns in SELECT for extra data

Subqueries are like having a helpful assistant who answers quick questions so you can solve bigger problems. Master them, and you’ll unlock powerful SQL capabilities!