Part 71: Introduction to Subqueries: Scalar, Column & Table Subqueries

A subquery is a query nested inside another SQL statement.

1. The Three Types of Subqueries

1. Scalar Subquery: Returns exactly one single value (1 row, 1 column). Can be used anywhere a value is expected.
2. Column / List Subquery: Returns 1 column with multiple rows. Used with IN, ANY, ALL.
3. Table Subquery (Derived Table): Returns multiple columns and multiple rows. Used in the FROM clause.

2. Scalar Subquery Example

-- Find employees earning more than the company average:
SELECT first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Key Takeaways

• Subqueries execute inner logic first and pass results to outer queries.

In Part 72, we explore Subqueries in the WHERE Clause: IN, ANY, SOME & ALL!