Subqueries, `EXISTS` and `NOT EXISTS`: use related results

One query may require the response of another: knowing if a customer has orders, comparing a price to the average, or retrieving a calculated value for the current queue.

A subquery is a query written inside another statement. EXISTS and NOT EXISTS specialize in answering whether the inner query finds at least one row.

The idea

The subquery can return:

a single value, used within an expression;
a list, used by operators such as IN;
a set whose mere existence is verified with EXISTS.

A correlated subquery uses columns from the outer query and is logically evaluated for each row in that set. Correlation is precisely that reference from the inner query to the current outer row.

`EXISTS`: check for presence

EXISTS returns true when the subquery finds at least one row. The columns selected inside it do not change that answer.

WHERE EXISTS
(
    Subconsulta
)

We want to retrieve customers who have at least one order:

SELECT
    c.IdCliente,
    c.Nombre
FROM dbo.Cliente AS c
WHERE EXISTS
(
    SELECT 1
    FROM dbo.Pedido AS p
    WHERE p.IdCliente = c.IdCliente
)
ORDER BY c.Nombre;

EXISTS does not need to return specific columns. SELECT 1 states that we are interested only in whether a related row exists.

`NOT EXISTS`: check for absence

NOT EXISTS returns true when the subquery finds no rows. It keeps the outer row only when no inner match exists.

WHERE NOT EXISTS
(
    Subconsulta
)

To find customers without orders:

SELECT
    c.IdCliente,
    c.Nombre
FROM dbo.Cliente AS c
WHERE NOT EXISTS
(
    SELECT 1
    FROM dbo.Pedido AS p
    WHERE p.IdCliente = c.IdCliente
)
ORDER BY c.Nombre;

ClientId	Name
3	Carla Ruiz

NOT EXISTS usually clearly expresses an absence search and does not have the problem that can occur with NOT IN when the internal list contains NULL.

Use a scalar subquery

A subquery that returns exactly one value is called scalar and can be part of an expression:

Expresion >
(
    SubconsultaEscalar
)

AVG(Precio) calculates the average of the prices arriving at the subquery. Since an aggregation without GROUP BY returns a single row, that average can be compared to the price of each outer product.

SELECT
    p.IdProducto,
    p.Nombre,
    p.Precio
FROM dbo.Producto AS p
WHERE p.Precio >
(
    SELECT AVG(Precio)
    FROM dbo.Producto
    WHERE Activo = 1
);

If the scalar subquery returns more than one row, SQL Server fails.

Subquery or `JOIN`

There is no automatic substitution. EXISTS communicates an existence test and does not multiply outer rows for each inner match. A JOIN is appropriate when we need columns on both sides, but can duplicate an outer row when multiple relationships exist.

The optimizer is the SQL Server component that compares strategies for running a query. It can transform different expressions into similar execution plans; a plan describes the strategy ultimately chosen. We first choose the form that represents the intent correctly and then measure whether performance requires attention.

What should be checked

In a correlated subquery, we must clearly identify which column belongs to the outer set. An alias is a short name assigned to a table or expression. It helps prevent ambiguous references and errors where a condition ends up comparing a column with itself.

We also check nullability—whether a column accepts NULL—when using IN and NOT IN, as well as cardinality, the number of rows returned, when expecting a single value.

Subqueries, EXISTS and NOT EXISTS: use related results

The idea

EXISTS: check for presence

NOT EXISTS: check for absence