`INTERSECT` and `EXCEPT`: Compare row sets

When we need to know which elements are shared by two queries or which appear in only one, we can express the comparison directly as an operation between sets.

INTERSECT preserves the rows present in both results. EXCEPT preserves those from the first query that do not appear in the second.

INTERSECT preserves the region shared by both sets. EXCEPT retains rows from the first set that do not appear in the second.

Rows present in both sets

INTERSECT keeps complete rows that appear in both results and removes duplicates.

Consulta1
INTERSECT
Consulta2

SELECT IdCliente
FROM dbo.Cliente
WHERE Activo = 1

INTERSECT

SELECT IdCliente
FROM dbo.Pedido
WHERE FechaPedido >= '20260101';

The result contains active customers who also placed at least one order since the indicated date. As a set operation, it removes duplicates.

The same intention can be expressed with EXISTS. The choice depends on whether we are comparing entire rows or mapping one query to another.

Rows that exist only in the first

EXCEPT keeps complete rows from the first query that do not appear in the second. Reversing the queries changes the question.

Consulta1
EXCEPT
Consulta2

SELECT IdProducto
FROM dbo.Producto
WHERE Activo = 1

EXCEPT

SELECT IdProducto
FROM dbo.DetallePedido;

This query shows active products that never appear in an order detail.

The order of the sets is decisive. If we reverse the queries, the question changes: we would obtain identifiers present in details that do not appear among the active products.

Compare more than one column

SELECT
    IdProducto,
    Nombre,
    Precio
FROM dbo.Producto

EXCEPT

SELECT
    IdProducto,
    Nombre,
    Precio
FROM dbo.ProductoImportado;

A row is considered equal only when all selected columns match. This can help validate an import, but does not by itself indicate which column changed.

Form rules

Both queries must return the same number of columns in the same order, with compatible types: types that SQL Server can convert to a common type. The final names come from the first query.

For this comparison, two NULL values are considered equal. NULL represents an absent or unknown value. This behavior deserves attention because it is not identical to writing NULL = NULL in a condition.

The ORDER BY, if we need it, is written only once at the end of the entire set:

SELECT IdCliente
FROM dbo.Cliente
WHERE Activo = 1

EXCEPT

SELECT IdCliente
FROM dbo.Pedido
ORDER BY IdCliente;

Versus `JOIN` and `NOT EXISTS`

INTERSECT and EXCEPT are expressive when we only want the common or different rows. A JOIN is more appropriate if we need columns from both sides, and EXISTS or NOT EXISTS allows for more detailed correlations and conditions.

Operations remove duplicates. If we need to preserve multiplicity, we must explicitly model the comparison; There is no equivalent INTERSECT ALL or EXCEPT ALL in SQL Server.

What should be checked

We review column order, implicit conversions, and collation rules, which determine how text is compared and sorted. We also measure the cost of removing duplicates. In large sets, indexes—structures that organize values to make rows easier to access—on the compared columns can substantially change the work required.

Before using EXCEPT to reconcile data—to compare two sources and locate differences—we determine whether we need different complete rows or changes by key. A key is the value or set of values that identifies an entity. These are different questions and produce different results.