Subqueries, Correlation, Views and Temporary Tables

1. Queries Inside Queries

A subquery is a SELECT nested inside another SQL statement. Its permitted result shape depends on where it appears. A scalar context requires one value, IN accepts a one-column set, EXISTS tests row presence and FROM accepts a table-shaped result.

Subqueries help express a larger question in stages:

SELECT employee_id, employee_name, salary
FROM employee
WHERE salary > (
    SELECT AVG(salary)
    FROM employee
);

The inner query computes the company average and the outer query selects employees above it.

2. Scalar Subqueries

A scalar subquery must return at most one row and exactly one column:

SELECT product_name,
       price,
       (SELECT AVG(price) FROM product) AS mean_price
FROM product;

One returned row supplies its value. Zero rows produce NULL. More than one row causes an error because SQL cannot choose one value arbitrarily.

An aggregate without GROUP BY is often useful because it returns one summary row even over empty input. A nonaggregate scalar subquery needs a uniqueness guarantee, such as a predicate on a candidate key.

3. Multirow Subqueries with IN

IN compares a value with the one-column result of a subquery:

SELECT employee_id, employee_name
FROM employee
WHERE department_id IN (
    SELECT department_id
    FROM department
    WHERE region = 'WEST'
);

Duplicate subquery values do not change IN's truth result. NULL can still affect three-valued logic when no equality match exists.

Use IN when membership is the natural question. A join may be preferable when columns from the matching rows are needed.

4. ANY and ALL

ANY, also written SOME, asks whether a comparison is true for at least one subquery value:

salary > ANY (SELECT salary FROM employee WHERE department_id = 10)

ALL asks whether it is true for every value:

salary > ALL (SELECT salary FROM employee WHERE department_id = 10)

Empty-set behavior follows logic: a comparison with ANY over no values is false, while a comparison with ALL over no values is true. NULL values can produce UNKNOWN, so the input domain should be understood.

Comparisons to MIN or MAX sometimes express the same intent more clearly, but empty and NULL behavior must be checked before rewriting.

5. Uncorrelated Subqueries

An uncorrelated subquery does not refer to the surrounding query:

SELECT *
FROM product
WHERE price = (SELECT MAX(price) FROM product);

Conceptually, it can be evaluated independently. The optimizer decides whether to compute it once, transform it, cache it or combine it into another plan.

Do not infer physical execution solely from the written nesting. SQL states a logical relationship, not a procedural loop.

6. Correlated Subqueries

A correlated subquery refers to a value from an outer query row:

SELECT e.employee_id, e.employee_name
FROM employee AS e
WHERE e.salary > (
    SELECT AVG(x.salary)
    FROM employee AS x
    WHERE x.department_id = e.department_id
);

The inner average is logically associated with the current outer employee's department. Aliases make the scope of each reference explicit.

Correlation describes semantics. The optimizer may transform the query into a join or another strategy rather than literally rerunning it once per row.

7. EXISTS

EXISTS is true when its subquery returns at least one row:

SELECT c.customer_id, c.customer_name
FROM customer AS c
WHERE EXISTS (
    SELECT 1
    FROM order_header AS o
    WHERE o.customer_id = c.customer_id
);

The selected expression inside EXISTS does not determine truth; only row presence matters. SELECT 1 is a convention and does not refer to a column named 1.

EXISTS acts like a semi-join. Multiple matching orders do not duplicate the customer because the outer row receives one Boolean result.

8. NOT EXISTS

NOT EXISTS is true when no qualifying subquery row exists:

SELECT d.department_id, d.department_name
FROM department AS d
WHERE NOT EXISTS (
    SELECT 1
    FROM employee AS e
    WHERE e.department_id = d.department_id
);

This finds departments without employees and expresses an anti-join directly.

It is generally safer than NOT IN when the subquery column may contain NULL. NOT EXISTS tests row matches, while NOT IN can become UNKNOWN because of one NULL among its comparison values.

9. Subqueries in the Select List

A scalar subquery may produce an output value:

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

This is readable for a small number of metrics. Several correlated aggregates may repeat related work. A grouped derived table joined once can be clearer and easier to optimize.

10. Derived Tables

A subquery in FROM is a derived table and must have an alias in MySQL:

SELECT d.department_name, x.mean_salary
FROM department AS d
JOIN (
    SELECT department_id, AVG(salary) AS mean_salary
    FROM employee
    GROUP BY department_id
) AS x
  ON x.department_id = d.department_id;

The derived table has its own columns and row grain. Here it produces one row per department before the outer join.

The optimizer may merge or materialize it according to query rules and cost.

11. Common Table Expressions

A common table expression gives a query result a name for one statement:

WITH department_pay AS (
    SELECT department_id,
           COUNT(*) AS employee_count,
           AVG(salary) AS mean_salary
    FROM employee
    GROUP BY department_id
)
SELECT d.department_name,
       p.employee_count,
       p.mean_salary
FROM department AS d
JOIN department_pay AS p
  ON p.department_id = d.department_id;

CTEs can improve readability and can be referenced where allowed. A recursive CTE can traverse hierarchies or generate sequences by combining an anchor query with a recursive member.

A CTE's written form does not guarantee materialization; inspect the plan when performance matters.

12. Subquery or Join

Several questions can be expressed either way. Use EXISTS for presence, a join for returning related columns and pre-aggregation when the many side must become one row per key.

Compare semantics before rewriting. A join may multiply outer rows, while EXISTS does not. A left join plus IS NULL can express an anti-join, but NULL tests must use a nonnullable matched key.

Performance depends on schema, indexes, statistics and optimizer transformations. Choose the clearest correct form, then inspect the execution plan.

13. Views

A view is a named stored query:

CREATE VIEW active_customer AS
SELECT customer_id, customer_name, email
FROM customer
WHERE status = 'ACTIVE';

Applications can query the view as a table. An ordinary view principally stores its definition, not an independent permanent copy of every result row.

Views can simplify repeated joins, expose a stable interface, hide columns and restrict rows. They do not automatically improve performance; the underlying query still has to be planned and executed.

14. View Column Names

Output columns should have stable unique names:

CREATE VIEW order_summary AS
SELECT c.customer_id,
       c.customer_name,
       COUNT(o.order_id) AS order_count,
       COALESCE(SUM(o.total_amount), 0) AS total_amount
FROM customer AS c
LEFT JOIN order_header AS o
  ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name;

Explicit aliases become the view interface. Changing them can break dependent queries even when underlying tables remain unchanged.

Avoid SELECT * in durable view definitions because added base columns can alter exposed structure.

15. Updatable Views

Some simple views map each view row directly to one base-table row and can support INSERT, UPDATE or DELETE. Views involving aggregation, GROUP BY, DISTINCT, UNION or many complex joins generally cannot map an update unambiguously.

Updatability is governed by exact MySQL rules. A view being queryable does not imply that every column can be modified.

Even for an updatable view, base-table constraints and privileges remain relevant.

16. WITH CHECK OPTION

Consider a view exposing active customers:

CREATE VIEW active_customer AS
SELECT customer_id, customer_name, status
FROM customer
WHERE status = 'ACTIVE'
WITH CHECK OPTION;

The check option prevents an insert or update through the view from creating a row that no longer satisfies its predicate. Without it, an update could make a row disappear from the view immediately after modification.

LOCAL and CASCADED behavior matters when views are built on other views. Use the required option and understand every underlying predicate.

17. Views and Security

A view can expose selected columns and rows while direct base-table access is withheld. This supports least-privilege design:

GRANT SELECT ON sales.active_customer TO report_user;

However, a view does not replace complete authorization. Definer and invoker security context, underlying privileges, functions, indirect inference and administrative rights all matter.

Sensitive data may still be inferred from counts or combinations. Security requires roles, privilege review, controlled routines, auditing and application access policies together.

18. Temporary Tables

A temporary table stores intermediate rows for the current session:

CREATE TEMPORARY TABLE recent_order AS
SELECT order_id, customer_id, total_amount
FROM order_header
WHERE ordered_at >= CURRENT_DATE - INTERVAL 30 DAY;

It can be indexed and queried by several subsequent statements. It is normally removed when the session ends and can be dropped earlier explicitly.

Temporary table scope is not the same as transaction scope. A commit does not necessarily remove it.

19. Temporary Table Design

Temporary tables are useful when:

• intermediate results are reused across statements;
• a process has several procedural stages;
• an index on materialized intermediate rows helps later work;
• the result needs independent manipulation.

They also incur creation, storage and population work. They can complicate connection pooling because session lifetime may outlast one logical request.

Do not assume materialization is automatically faster than one well-optimized query. Measure representative data and inspect plans.

20. Views, Derived Tables, CTEs and Temporary Tables

These constructs differ in lifetime and storage:

• a view is a persistent schema object containing a query definition;
• a derived table exists as part of one statement;
• a CTE is a named expression scoped to one statement;
• a temporary table is a session-scoped table containing intermediate rows.

Use a view for a reusable logical interface, a derived table or CTE for statement structure and a temporary table when stored intermediate state is genuinely needed across operations.

21. Indexes for Correlation and Existence

Correlated predicates often compare a child foreign key with an outer parent key:

WHERE e.department_id = d.department_id

An index on employee(department_id) can make matching-row discovery efficient. Composite indexes should follow all important equality and range predicates.

EXISTS can stop logically after finding a match, but actual cost still depends on access paths and estimates. Inspect EXPLAIN rather than assuming every subquery is slow or every join is fast.

22. Safe Query Reasoning

For each subquery, state its shape: scalar, one-column multirow or table. Identify outer references and confirm their aliases. Prove scalar uniqueness rather than relying on current data.

For absence, prefer NOT EXISTS when nullable values are possible. For views, document output columns, updatability, check-option behavior and security context. For temporary tables, define lifetime, cleanup and reuse.

Nested SQL is reliable when every level has a clear row grain, correlation key and result contract. The syntax may be nested, but the reasoning should remain explicit.