Registros huérfanos: cómo encontrarlos con SQL (y evitarlos)
|
6
minuto de lectura
Una fila de pagos dice account_id = 884213. La tabla accounts no tiene esa cuenta. No salta ningún error, la carga termina en verde y el informe de sucursales de la mañana siguiente queda corto exactamente en ese pago, porque el informe une pagos con cuentas y el join lo descarta sin avisar. Un registro huérfano es una fila hija cuyo valor de clave foránea no tiene ninguna fila coincidente en la tabla padre a la que hace referencia.
Los registros huérfanos, o filas huérfanas, aparecen allí donde las claves foráneas no se aplican: almacenes de datos (data warehouses), capas de staging, réplicas, pipelines que cargan los hijos antes que los padres. Encontrarlos es una tarea SQL estándar: el anti join. Hay cuatro formas habituales de escribirlo, y una de ellas informa de "ningún huérfano" en cuanto aparece un solo NULL.
Esta guía cubre los patrones, las trampas y cómo convertir la consulta en una comprobación que se ejecute en cada carga. Para el concepto en sí, consulte ¿Pueden sus datos seguir encontrando a sus padres? Entender la integridad referencial.
Puntos clave
Los inner joins ocultan los registros huérfanos: las filas hijas sin coincidencia desaparecen del resultado en lugar de generar un error.
Encuentre los huérfanos con un anti join:
LEFT JOIN … IS NULL,NOT EXISTSoEXCEPTsobre claves distintas.Evite
NOT INsobre una columna que admite NULL: un solo NULL en la subconsulta devuelve cero filas, lo que parece un resultado limpio.Compare las claves compuestas con todas sus columnas a la vez, y decida conscientemente si una clave foránea NULL es un error.
Una consulta que hay que acordarse de ejecutar no es un control. En digna, el mismo anti join es una única regla Referential Integrity que se ejecuta con cada inspección.
Índice de contenidos
¿Qué es un registro huérfano?
¿Por qué los inner joins ocultan los registros huérfanos?
¿Cómo se encuentran registros huérfanos con SQL?
LEFT JOIN … IS NULL
NOT EXISTS
EXCEPT (MINUS) sobre claves distintas
¿Por qué NOT IN no devuelve filas cuando hay un NULL?
¿Qué patrón de anti join debería usar?
¿Cómo se tratan las claves compuestas y las claves foráneas NULL?
¿Cómo se encuentran registros huérfanos en tablas grandes?
¿Y si la tabla padre está en otra base de datos?
¿Cómo se convierte una consulta de huérfanos en una comprobación permanente?
Encuéntrelos una vez y después manténgalos fuera
¿Qué es un registro huérfano?
Un registro huérfano es una fila de una tabla hija cuya clave foránea apunta a una clave padre que no existe: un pago cuyo account_id no está en accounts, o una administración de medicamento cuyo product_code no está en medications. La fila puede ser perfectamente válida por sí sola. Lo que está roto es la relación.
Los huérfanos siempre están en el lado hijo; una cuenta sin pagos es normal. Las causas habituales son corrientes: el padre se eliminó o nunca se cargó, el hijo llegó antes que la carga de datos maestros de mañana, o los formatos de clave difieren ('00884213' frente a 884213, o un espacio al final).
Las bases de datos operacionales como PostgreSQL u Oracle aplican las claves foráneas declaradas, pero las copias en el almacén de datos normalmente no heredan las restricciones. La mayoría de los almacenes de datos en la nube aceptan una declaración FOREIGN KEY sin aplicarla; la documentación de BigQuery indica que no aplica las restricciones de clave primaria ni de clave foránea. Explicamos por separado por qué Snowflake, BigQuery, Redshift y Databricks no aplican las claves foráneas.
¿Por qué los inner joins ocultan los registros huérfanos?
Un inner join devuelve solo las filas que coinciden en ambos lados, así que una fila hija sin padre simplemente no aparece en el resultado. No hay error, ni aviso, ni un NULL que llame la atención. Los totales salen más bajos de lo que deberían, y nada en el propio informe muestra la diferencia.
Cada pago cuyo account_id falta en accounts desaparece antes del SUM. La forma más rápida de ver la diferencia es contar de las dos maneras:
Si account_id es único en accounts, la diferencia son sus huérfanos más las filas con account_id NULL.
Las claves declaradas pero no comprobadas pueden empeorarlo. El planificador de Amazon Redshift asume que las claves declaradas son válidas, y AWS advierte de que las claves no válidas pueden hacer que algunas consultas devuelvan resultados incorrectos.
¿Cómo se encuentran registros huérfanos con SQL?
Los registros huérfanos se encuentran con un anti join: una consulta que devuelve las filas hijas para las que no existe ninguna fila padre coincidente. En SQL se escribe como LEFT JOIN … WHERE parent_key IS NULL, como NOT EXISTS o como EXCEPT sobre los valores de clave distintos. Con claves no nulas, los tres coinciden.
LEFT JOIN … IS NULL
Compruebe una columna del padre que no pueda ser NULL en una fila coincidente, idealmente la clave del join. Ponga las condiciones sobre el padre en la cláusula ON: WHERE a.status = 'ACTIVE' nunca es verdadero para una fila sin coincidencia, así que la consulta no devolvería nada.
NOT EXISTS
Se lee igual que la pregunta. Las claves padre duplicadas no multiplican filas, y los NULL en accounts.account_id no pueden romperla: una igualdad con NULL nunca coincide.
EXCEPT (MINUS) sobre claves distintas
Devuelve los valores de clave distintos que faltan, no las filas. A menudo es la mejor primera pregunta: un puñado de cuentas ausentes puede explicar miles de pagos huérfanos. Oracle ha escrito tradicionalmente el operador como MINUS; BigQuery exige EXCEPT DISTINCT. Los operadores de conjuntos tratan dos NULL como iguales, un motivo más para filtrar explícitamente las claves NULL.
¿Por qué NOT IN no devuelve filas cuando hay un NULL?
NOT IN no devuelve ninguna fila si su subconsulta contiene aunque sea un solo NULL, porque la lógica trivalente de SQL hace que toda comparación con ese NULL sea desconocida. x NOT IN (1, 2, NULL) significa x <> 1 AND x <> 2 AND x <> NULL. El último término nunca es verdadero, así que la condición completa nunca es verdadera.
Para un pago cuya cuenta existe, una comparación es falsa y la fila se excluye correctamente. Para una cuenta que falta, todas las comparaciones con claves reales son verdaderas, pero la comparación con NULL es desconocida, así que la condición es desconocida, y WHERE solo conserva las filas verdaderas. El resultado es un conjunto vacío, indistinguible de "ningún huérfano".
Falla en silencio, con un falso visto bueno, y las columnas de clave del almacén de datos a menudo no se declaran NOT NULL. Los pagos cuyo propio account_id es NULL tampoco cumplen nunca la condición. Use NOT EXISTS, o al menos añada WHERE a.account_id IS NOT NULL dentro de la subconsulta.
¿Qué patrón de anti join debería usar?
Use NOT EXISTS por defecto para comprobaciones de huérfanos a nivel de fila, EXCEPT cuando quiera la lista de valores de clave que faltan y LEFT JOIN … IS NULL cuando también quiera los recuentos de coincidencias en la misma pasada. Use NOT IN solo cuando ambas columnas estén garantizadas como no nulas.
Patrón | Devuelve | NULL en la clave padre | Clave foránea NULL en el hijo | Legibilidad | Rendimiento típico |
|---|---|---|---|---|---|
LEFT JOIN … IS NULL | Filas hijas | Seguro | Se informa como huérfano salvo que se filtre | Conocido; la intención está en la cláusula WHERE | Normalmente se planifica como anti join |
NOT EXISTS | Filas hijas | Seguro | Se informa como huérfano salvo que se filtre | Se lee como la pregunta | Normalmente se planifica como anti join |
EXCEPT / MINUS | Valores de clave distintos | Seguro | Se devuelve una vez como NULL salvo que se filtre | Breve y claro para listas de claves | Deduplica ambos lados; bueno para resúmenes a nivel de clave |
NOT IN | Filas hijas | Inseguro: un NULL devuelve cero filas | Se excluye en silencio | Se lee bien, induce a error | Bien en columnas no nulas; puede obtener un plan peor en columnas que admiten NULL |
La mayoría de los optimizadores planifican LEFT JOIN y NOT EXISTS de la misma forma; revise el plan en su plataforma.
¿Cómo se tratan las claves compuestas y las claves foráneas NULL?
Para una clave compuesta, compare todas las columnas de la clave juntas en un solo predicado, nunca columna a columna: una fila es huérfana cuando falta su combinación, aunque cada valor exista por separado en algún lugar. Las claves foráneas NULL requieren una decisión consciente antes de contarlas: referencia ausente o vacío legítimo.
Supongamos que cada hospital tiene su propio formulario de medicamentos, con clave (hospital_id, product_code):
Dos comprobaciones separadas, "el hospital existe" y "el producto existe", pasan ambas para un producto que solo figura en el hospital A y se administra en el hospital B. Solo el predicado combinado lo detecta. EXCEPT maneja las claves compuestas de forma natural. No concatene las claves en una sola cadena: '1' || '23' y '12' || '3' colisionan.
Un pago con account_id NULL no apunta a una cuenta que falta; no apunta a ningún sitio. Un pago debe tener una cuenta, mientras que una referencia opcional como referring_doctor_id puede estar vacía. Excluya los NULL de la consulta de huérfanos y cuéntelos aparte:
¿Cómo se encuentran registros huérfanos en tablas grandes?
En tablas grandes, cuente antes de listar, compare valores de clave distintos en lugar de cada fila y limite el lado hijo a la última carga o partición. Mantenga completo el lado padre: un pago registrado hoy puede hacer referencia a una cuenta abierta hace años, así que nunca filtre el padre por fecha de carga.
Cuente primero. Ejecute el anti join como
COUNT(*). Recupere filas solo cuando el recuento no sea cero, y entonces con un límite.Compare claves distintas. Reduzca primero el lado hijo a
SELECT DISTINCT account_id. Las claves distintas suelen ser muchas menos que las filas.Filtre a la última carga. Limite el hijo a la partición o
load_datemás reciente para que el motor pueda podar particiones.Mantenga comparable la columna del join. El mismo tipo de dato en ambos lados, sin
CASTniTRIMen el predicado, que pueden impedir el uso de índices y la poda de particiones. Corrija los formatos en la carga; consulte nuestra guía de limpieza de datos en SQL.Registre el resultado. Guarde la fecha, las filas evaluadas y los huérfanos encontrados para ver la tendencia.
Esto cuenta claves que faltan, no filas huérfanas; recupere después las filas de esas claves.
¿Y si la tabla padre está en otra base de datos?
Un anti join solo funciona cuando un mismo motor de consultas puede leer ambas tablas. Si los pagos están en el almacén de datos y las cuentas en la base de datos del core bancario, el SQL simple no puede ver ambas, así que hay que copiar un lado, usar un database link o una consulta federada, o ejecutar la comprobación en una herramienta que pueda acceder a ambas conexiones.
Una tabla maestra copiada es otro pipeline con su propio retraso: una copia desactualizada informa de falsos huérfanos o pasa por alto los reales. Los database links dependen del soporte de la plataforma y requieren aprobación de seguridad para cada nueva conexión.
¿Cómo se convierte una consulta de huérfanos en una comprobación permanente?
Una consulta de huérfanos se convierte en una comprobación permanente ejecutándola en cada carga, registrando los recuentos de filas correctas y fallidas, fijando un umbral de fallo y dejando las filas fallidas donde el equipo responsable pueda verlas. En digna es una única regla Referential Integrity, configurada en un diálogo, sin SQL que escribir.
digna Data Validation tiene tres tipos de regla: Rule, Uniqueness y Referential Integrity. La referencial es el anti join de este artículo: dadas unas columnas en una fuente de datos y un conjunto equivalente en otra, digna conserva los valores distintos de la otra tabla y marca como fallida cualquier fila que no se une. Se ejecuta dentro de su base de datos de origen; sus datos nunca salen de su infraestructura. Desde Release 2026.01 funciona entre distintas conexiones de bases de datos dentro del mismo proyecto, sin replicar datos.
Las capturas de pantalla siguientes usan los datos de demostración de digna para Danubia Kliniken, un grupo hospitalario austriaco ficticio.
El 2026-04-22, las unidades registraron 82 administraciones de Coavira 2.5 mg (código de producto 3858646), que aún no estaba en el maestro de productos de farmacia. Todos los informes que unían dosis con productos mostraban 0 dosis de él, mientras el personal de enfermería había administrado 82. La regla hc_product_in_master falló: pasaron 4.244 de 4.326 filas. A mano, necesitaría esto:
La vista Invalid Records muestra ese conjunto de resultados sin que nadie tenga que escribirlo:
Invalid Records, filtrado por Failed: cada dosis huérfana con hospital, unidad, servicio, código de producto y nombre del medicamento.
Configuración de la regla:
Configuration → fuente de datos
hospital_medication_administrations→ pestaña Data Validation → Add Rule. Se abre el diálogo Add Data Validation Rule.Introduzca un Name (
hc_product_in_master) y una Description.Establezca Type en Referential Integrity y elija en Attributes
product_code.En must exist in, elija la Data Source
hospital_medicationsy sus Attributesproduct_code. Para una clave compuesta, elija varias columnas en ambos lados en el mismo orden; las listas de distinta longitud se rechazan.Elija Threshold Mode Absolute o Relative y fije el Info threshold y el Warn threshold (aquí: Absolute, Info 0, Warn 1). Guarde. La regla se ejecuta con cada inspección, programada o bajo demanda.
La regla completa: dos listas de atributos, una fuente de datos de destino y dos umbrales.
Referential Integrity omite los NULL, igual que el filtro IS NOT NULL anterior; si la presencia es obligatoria, añada una Rule aparte con product_code IS NOT NULL. Por encima del Info threshold el estado es Uncertain, por encima del Warn threshold Failed, así que con Info en 0 cualquier huérfano saca la regla de Passed. Los registros fallidos son la misma consulta con la condición de paso negada, se pueden exportar, y los resultados pueden notificar al equipo responsable de los datos.
Desde Release 2026.06, las reglas también pueden gestionarse como código mediante el SDK de Python (pip install digna-sdk). Para el recorrido completo, consulte cómo configurar una comprobación de integridad referencial en digna o el vídeo de configuración de 2:26.
Encuéntrelos una vez y después manténgalos fuera
Para investigar registros huérfanos: NOT EXISTS para filas, EXCEPT para claves que faltan, nunca NOT IN sobre una columna que admite NULL, y una decisión consciente sobre los NULL. Mantenerlos fuera exige más: aplicar las claves foráneas donde la base de datos lo permita, cargar primero los padres, tratar explícitamente los datos maestros que llegan tarde y ejecutar la comprobación referencial en cada carga.
Para ver esa comprobación funcionando sobre sus propias tablas, dentro de su propia infraestructura, solicite una demo con el equipo de digna.
Preguntas frecuentes
¿Cómo encuentro registros huérfanos en SQL?
Use un anti join que devuelva las filas hijas sin padre coincidente. La forma más robusta es NOT EXISTS con una subconsulta correlacionada sobre la clave; LEFT JOIN … WHERE parent_key IS NULL da el mismo resultado. Filtre primero las claves foráneas NULL y cuéntelas como una comprobación de completitud aparte.
¿Por qué NOT IN no devuelve filas cuando la subconsulta contiene un NULL?
La razón es la lógica trivalente. x NOT IN (1, 2, NULL) se expande a x <> 1 AND x <> 2 AND x <> NULL, y la última comparación es desconocida, nunca verdadera. WHERE solo conserva las filas verdaderas, así que la consulta no devuelve nada, lo que parece exactamente un resultado limpio.
¿Es NOT EXISTS más rápido que LEFT JOIN IS NULL?
Normalmente ninguno es más rápido: la mayoría de los optimizadores modernos planifican ambos como un anti join, así que la elección se reduce a la legibilidad. NOT IN es la excepción. En columnas que admiten NULL puede obtener un plan peor, y no devuelve ninguna fila cuando la subconsulta contiene un NULL.
¿Cuál es la diferencia entre un registro huérfano y una clave foránea NULL?
Un registro huérfano apunta a una clave padre que no existe, mientras que una clave foránea NULL no apunta a ningún sitio. Necesitan comprobaciones distintas: integridad referencial para los huérfanos, y una regla IS NOT NULL donde la referencia es obligatoria. La regla Referential Integrity de digna omite los NULL precisamente por este motivo.
¿Cómo puedo comprobar automáticamente los registros huérfanos después de cada carga?
Convierta el anti join en una comprobación programada con umbrales y resultados almacenados. En digna es una única regla Referential Integrity en Data Validation: elija las columnas, elija la fuente de datos en la que deben existir, fije los umbrales Info y Warn, y guarde. Se ejecuta dentro de su base de datos con cada inspección.
