Detect orphan records

A load contains orders whose customer does not appear in the received catalog. It can also occur on a basis without foreign keys, after an incomplete migration, or when the relationship is validated only in the application.

What we need to distinguish

The child table contains the reference, and the parent table contains the expected row. An orphan reference retains a child key value but finds no corresponding parent row.

We will look for non-null child keys that do not have a corresponding parent row. A null key expresses no relationship; a key with value but no match expresses an orphan reference. It is advisable to measure them separately.

Query

/*
    Antes de ejecutar:
        Revisa la base activa, los nombres de objetos, los parámetros y los permisos.
        Adapta el ejemplo a tu modelo y pruébalo primero en un ambiente controlado.
*/

SET NOCOUNT ON;
GO
-- Registros con referencia inexistente
SELECT
    pi.IdPedidoImportado,
    pi.IdClienteOrigen,
    pi.FechaPedido,
    pi.Total
FROM dbo.PedidoImportado AS pi
WHERE pi.IdClienteOrigen IS NOT NULL
  AND NOT EXISTS
  (
      SELECT 1
      FROM dbo.ClienteImportado AS ci
      WHERE ci.IdClienteOrigen = pi.IdClienteOrigen
  )
ORDER BY
    pi.IdClienteOrigen,
    pi.IdPedidoImportado;
GO

-- Variante con LEFT JOIN
SELECT
    pi.IdPedidoImportado,
    pi.IdClienteOrigen,
    pi.FechaPedido,
    pi.Total
FROM dbo.PedidoImportado AS pi
LEFT JOIN dbo.ClienteImportado AS ci
    ON ci.IdClienteOrigen = pi.IdClienteOrigen
WHERE pi.IdClienteOrigen IS NOT NULL
  AND ci.IdClienteImportado IS NULL;
GO

-- Registros sin clave de referencia
SELECT COUNT_BIG(*) AS PedidosSinClaveDeCliente
FROM dbo.PedidoImportado
WHERE IdClienteOrigen IS NULL;
GO

Parameters or permissions

A foreign key is a constraint that requires a reference to point to a valid row; this check also works when the relationship is not declared.

Change the tables and columns for the relationship you want to check. The parent’s evaluated column must be non-null or supported by a rule that allows the mismatch to be recognized. In the variant with LEFT JOIN we use the primary keyIdClienteImportado for this check. Both queries require reading over the two tables. On large sets, check that the relationship columns have appropriate indexes before running a broad search.

How to read the result

Each row is an order with a reported customer ID, but no matching customer. NOT EXISTS directly expresses that absence and avoids multiplying results if the parent source contains repeated keys. The variant with LEFT JOIN is useful when we also need to display columns from the parent during other validations. If the parent table has multiple rows for a key, a join can duplicate the detail; NOT EXISTS only responds if at least one exists. To measure null child values without mixing them with orphans, use another query: SELECT COUNT_BIG(*) AS OrdersWithoutCustomerKey FROM dbo.ImportedOrder WHERE OriginCustomerId IS NULL;

What this does establish

It allows identifying non-null references that do not find a parent under the queried relationship.

What this still does not establish

It does not explain why the parent is missing nor does it determine that the child should be removed. The catalog load could be incomplete, the identifier could require conversion, or the relationship could belong to another system. A declared foreign key would normally prevent these cases, but is no substitute for reviewing imported data or relationships built outside the base.

Next reasonable check

Compare the origin and loading date of both sets. Check whether keys need normalization and confirm whether a foreign key, soft delete process, or equivalence table exists before correcting the data.

Search SQL Server