LEFT, RIGHT and FULL JOIN: Keep unmatched rows

An INNER JOIN works well when we are interested only in related rows. The problem changes when we also need to see what was left out: customers without orders, products that have never been sold, or records that appear in one source but not another.

Outer joins keep those rows even when they find no matching row. Columns from the unmatched side are filled with NULL, which represents an unknown or unavailable value.

The idea

The direction of the union indicates which set will be kept:

• LEFT JOIN preserves all rows of the table written to the left.
• RIGHT JOIN preserves all rows in the table written to the right.
• FULL JOIN preserves the rows on both sides, whether they coincided or not.

LEFT JOIN: preserve the left side

LEFT JOIN keeps every row from the set written before the join and adds columns from matches found on the right.

FROM dbo.TablaIzquierda AS i
LEFT JOIN dbo.TablaDerecha AS d
    ON d.IdRelacionado = i.Id

We want to review all customers, even those who have not placed orders:

SELECT
    c.IdCliente,
    c.Nombre,
    p.IdPedido,
    p.Estado
FROM dbo.Cliente AS c
LEFT JOIN dbo.Pedido AS p
    ON p.IdCliente = c.IdCliente
ORDER BY
    c.IdCliente,
    p.IdPedido;

Identifiers 1, 2, 4, and 5 find orders. The identifier 3, corresponding to Carla, also remains because LEFT JOIN preserves the entire Cliente set.

Carla remains because Cliente is on the left. Since there is no order related to its identifier, the columns coming from Pedido contain NULL.

This behavior allows you to specifically find customers without orders:

SELECT
    c.IdCliente,
    c.Nombre
FROM dbo.Cliente AS c
LEFT JOIN dbo.Pedido AS p
    ON p.IdCliente = c.IdCliente
WHERE p.IdPedido IS NULL;

RIGHT JOIN

RIGHT JOIN retains the table written to the right. With Cliente before RIGHT JOIN and Pedido after, all rows from Pedido are preserved:

FROM dbo.TablaIzquierda AS i
RIGHT JOIN dbo.TablaDerecha AS d
    ON d.IdRelacionado = i.Id

SELECT
    c.IdCliente,
    c.Nombre,
    p.IdPedido,
    p.Estado
FROM dbo.Cliente AS c
RIGHT JOIN dbo.Pedido AS p
    ON p.IdCliente = c.IdCliente
ORDER BY
    p.IdPedido;

RIGHT JOIN retains all rows from Pedido. When a row does not find a customer, the columns from Cliente are completed with NULL.

Carla is left out because she has no order. With the data in the example, all orders find a customer; therefore the unique region of Pedido does not contain identifiers.

To identify only the orders that do not find a customer, we filter the rows without a match:

SELECT
    p.IdPedido,
    p.IdCliente,
    p.Estado
FROM dbo.Cliente AS c
RIGHT JOIN dbo.Pedido AS p
    ON p.IdCliente = c.IdCliente
WHERE c.IdCliente IS NULL
ORDER BY p.IdPedido;

This second query represents only the region unique to Pedido. In the current data it returns no rows; If there was an order without a related customer, it would appear here with its data of Pedido.

The same operation can be written with LEFT JOIN if the order of the tables is swapped. The preserved set depends on the table that remains on the side indicated by the union.

FULL JOIN

FULL JOIN is useful when comparing two sets. Preserve matches, left-exclusive rows, and right-exclusive rows:

FROM dbo.TablaIzquierda AS i
FULL JOIN dbo.TablaDerecha AS d
    ON d.IdRelacionado = i.Id

SELECT
    a.Clave,
    a.Valor AS ValorAnterior,
    n.Valor AS ValorNuevo
FROM dbo.DatosAnteriores AS a
FULL JOIN dbo.DatosNuevos AS n
    ON n.Clave = a.Clave;

All three regions are preserved here: rows that only exist in DatosAnteriores, matches, and rows that only exist in DatosNuevos.

What should be checked

The optional table is the side of an outer join that may find no match and therefore contribute columns containing NULL. A condition on that side can accidentally turn a LEFT JOIN into the practical equivalent of an INNER JOIN:

-- Los clientes sin pedido quedan fuera porque p.Estado es NULL.
WHERE p.Estado = 'Confirmado';

When we want to preserve them, that condition can be part of ON or it must explicitly consider the null value. The choice depends on which rows we intend to keep, not on a mechanical rule.

Related to

• INNER JOIN: Keep matches only.
• NULL: represents columns without correspondence.
• EXISTS and NOT EXISTS: check for related rows.
• INTERSECT and EXCEPT: compare compatible sets.