transact - SQL "select where not in subquery" no arroja resultados

where condition sql server

Question

Descargo de responsabilidad: He descubierto el problema (creo), pero quería agregar este problema a Stack Overflow ya que no pude (fácilmente) encontrarlo en ningún lado. Además, alguien podría tener una mejor respuesta que yo.

Tengo una base de datos donde otras tablas hacen referencia a una tabla "Común". Quería ver qué registros en la tabla común se quedaron huérfanos (es decir, no tenían referencias de ninguna de las otras tablas).

Ejecuté esta consulta:

select *
from Common
where common_id not in (select common_id from Table1)
and common_id not in (select common_id from Table2)

Sé que hay registros huérfanos, pero no se devolvieron los registros. Por qué no?

(Esto es SQL Server, si es importante)

Answer 1

Justo al lado de la parte superior de mi cabeza...

select c.commonID, t1.commonID, t2.commonID
from Common c
     left outer join Table1 t1 on t1.commonID = c.commonID
     left outer join Table2 t2 on t2.commonID = c.commonID
where t1.commonID is null 
     and t2.commonID is null

Realicé algunas pruebas y aquí estaban mis resultados, la respuesta de wrt @ patmortech y los comentarios de @rexem.

Si Table1 o Table2 no están indexados en commonID, se obtiene un escaneo de tabla, pero la consulta de @patmortech sigue siendo dos veces más rápida (para una tabla maestra de 100K filas).

Si ninguno de los dos está indexado en commonID, obtendrá dos escaneos de tabla y la diferencia es insignificante.

Si ambos están indexados en commonID, la consulta "no existe" se ejecuta en 1/3 del tiempo.

Answer 2

Si desea que el mundo sea un lugar booleano de dos valores, debe evitar el caso nulo (tercer valor) usted mismo.

No escriba cláusulas IN que permitan nulos en el lado de la lista. ¡Filtralos!

common_id not in
(
  select common_id from Table1
  where common_id is not null
)

Answer 3

Supongamos estos valores para common_id:

Common - 1
Table1 - 2
Table2 - 3, null

Queremos que la fila en Common regrese, porque no existe en ninguna de las otras tablas. Sin embargo, el nulo arroja una llave inglesa.

Con esos valores, la consulta es equivalente a:

select *
from Common
where 1 not in (2)
and 1 not in (3, null)

Eso es equivalente a:

select *
from Common
where not (1=2)
and not (1=3 or 1=null)

Aquí es donde empieza el problema. Al comparar con un valor nulo, la respuesta es desconocida . Entonces, la consulta se reduce a

select *
from Common
where not (false)
and not (false or unkown)

falso o desconocido es desconocido:

select *
from Common
where true
and not (unknown)

verdadero y no desconocido también es desconocido:

select *
from Common
where unknown

La condición where no devuelve registros donde el resultado no se conoce, por lo que no recuperamos ningún registro.

Una forma de lidiar con esto es utilizar el operador exists en lugar de in. Exists nunca devuelve desconocido porque opera en filas en lugar de en columnas. (Una fila existe o no existe, ¡ninguna de esta ambigüedad nula en el nivel de la fila!)

select *
from Common
where not exists (select common_id from Table1 where common_id = Common.common_id)
and not exists (select common_id from Table2 where common_id = Common.common_id)

Answer 4

Table1 o Table2 tiene algunos valores nulos para common_id. Use esta consulta en su lugar:

select *
from Common
where common_id not in (select common_id from Table1 where common_id is not null)
and common_id not in (select common_id from Table2 where common_id is not null)

Answer 5

esto funcionó para mí :)

seleccionar * de Común

dónde

common_id no está en (seleccione ISNULL (common_id, ''dummy-data'') de la Tabla1)

y common_id no está en (seleccione ISNULL (common_id, ''dummy-data'') de la Tabla 2)

Answer 6

Actualizar:

Estos artículos en mi blog describen las diferencias entre los métodos en más detalle:

• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL : SQL Server
• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL : PostgreSQL
• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL : Oracle
• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL : MySQL

Hay tres formas de hacer una consulta de este tipo:

• LEFT JOIN / IS NULL :

  SELECT  *
  FROM    common
  LEFT JOIN
          table1 t1
  ON      t1.common_id = common.common_id
  WHERE   t1.common_id IS NULL

• NOT EXISTS :

  SELECT  *
  FROM    common
  WHERE   NOT EXISTS
          (
          SELECT  NULL
          FROM    table1 t1
          WHERE   t1.common_id = common.common_id
          )

• NOT IN :

  SELECT  *
  FROM    common
  WHERE   common_id NOT IN
          (
          SELECT  common_id
          FROM    table1 t1
          )

Cuando table1.common_id no puede table1.common_id , todas estas consultas son semánticamente las mismas.

Cuando es anulable, NOT IN es diferente, ya que IN (y, por lo tanto, NOT IN ) devuelve NULL cuando un valor no coincide con nada en una lista que contiene un NULL .

Esto puede ser confuso, pero puede volverse más obvio si recordamos la sintaxis alternativa para esto:

common_id = ANY
(
SELECT  common_id
FROM    table1 t1
)

El resultado de esta condición es un producto booleano de todas las comparaciones dentro de la lista. Por supuesto, un único valor NULL produce el resultado NULL que representa el resultado NULL también.

Nunca podemos decir definitivamente que common_id no es igual a nada de esta lista, ya que al menos uno de los valores es NULL .

Supongamos que tenemos estos datos:

common

--
1
3

table1

--
NULL
1
2

LEFT JOIN / IS NULL y NOT EXISTS devolverá 3 , NOT IN no devolverá nada (ya que siempre se evaluará como FALSE o NULL ).

En MySQL , en el caso de una columna que no admite nulos, LEFT JOIN / IS NULL y NOT IN son un poco (varios por ciento) más eficientes que NOT EXISTS . Si la columna es nulable, NOT EXISTS es la más eficiente (nuevamente, no mucho).

En Oracle , las tres consultas producen los mismos planes ( ANTI JOIN ).

En SQL Server , NOT IN / NOT EXISTS es más eficiente, ya que LEFT JOIN / IS NULL no se puede optimizar a ANTI JOIN por su optimizador.

En PostgreSQL , LEFT JOIN / IS NULL y NOT EXISTS son más eficientes que NOT IN , ya que están optimizados para un Anti Join , mientras que NOT IN utiliza un hashed subplan (o incluso un subplan simple si la subconsulta es demasiado grande para hash)

Answer 7

SELECT T.common_id
  FROM Common T
       LEFT JOIN Table1 T1 ON T.common_id = T1.common_id
       LEFT JOIN Table2 T2 ON T.common_id = T2.common_id
 WHERE T1.common_id IS NULL
   AND T2.common_id IS NULL

Answer 8

select *
from Common c
where not exists (select t1.commonid from table1 t1 where t1.commonid = c.commonid)
and not exists (select t2.commonid from table2 t2 where t2.commonid = c.commonid)

Answer 9

select *,
(select COUNT(ID)  from ProductMaster where ProductMaster.CatID = CategoryMaster.ID) as coun 
from CategoryMaster