SQL "sélectionner les sous-requêtes" ne renvoie aucun résultat

Question

Avertissement: j'ai compris le problème (je pense), mais je voulais ajouter ce sujet pour Stack Overflow car je ne pouvais pas (facilement) le trouver n'importe où. Aussi, quelqu'un pourrait avoir une meilleure réponse que moi.

J'ai une base de données où une table "Communes" est référencé par plusieurs autres tables. Je voulais voir ce que les enregistrements dans la table Commune étaient des orphelins (c'est à dire, n'avait pas de références de toutes les autres tables).

J'ai couru cette requête:

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

Je sais qu'il existe des enregistrements orphelins, mais pas de dossiers ont été retournés. Pourquoi pas?

(C'est SQL Server, si il le faut.)

Answer 1

Mise à jour:

Ces articles dans mon blog décrire les différences entre les méthodes plus en détail:

• 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

---

Il y a trois façons de faire une telle requête:

• 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
          )
  

Lors de l' table1.common_id n'est pas nullable, toutes ces requêtes sont sémantiquement identiques.

Quand il est nullable, NOT IN est différent, puisqu' IN (et, par conséquent, NOT IN) rendement NULL lorsqu'une valeur ne correspond pas à quoi que ce soit dans une liste contenant un NULL.

Cela peut être déroutant, mais peut devenir plus évident si nous nous rappelons la syntaxe alternative à cela:

common_id = ANY
(
SELECT  common_id
FROM    table1 t1
)

Le résultat de cette condition est un booléen produit de toutes les comparaisons à l'intérieur de la liste. Bien sûr, un seul NULL de la valeur des rendements de l' NULL résultat qui rend la totalité du résultat NULL trop.

Nous n'avons jamais ne peut pas dire certainement que l' common_id n'est pas égal à quoi que ce soit de cette liste, depuis au moins l'une des valeurs est - NULL.

Supposons que nous disposons de ces données:

common

--
1
3

table1

--
NULL
1
2

LEFT JOIN / IS NULL et NOT EXISTS sera de retour 3, NOT IN sera de retour rien (puisqu'il sera toujours correspondre à la FALSE ou NULL).

En MySQL, en cas de non-colonne nullable, LEFT JOIN / IS NULL et NOT IN sont un peu (quelques pour cent) plus efficace que l' NOT EXISTS. Si la colonne est nullable, NOT EXISTS est le plus efficace (encore une fois, pas beaucoup).

En Oracle, tous les trois requêtes de rendement mêmes plans ( ANTI JOIN).

En SQL Server, NOT IN / NOT EXISTS sont plus efficaces, car LEFT JOIN / IS NULL ne peut pas être optimisé pour un ANTI JOIN par son optimiseur.

En PostgreSQL, LEFT JOIN / IS NULL et NOT EXISTS sont plus efficaces que d' NOT IN, sinus, ils sont optimisés pour un Anti Join, tandis que l' NOT IN utilise hashed subplan (ou même un simple subplan si la sous-requête est trop grande pour le hachage)

Answer 2

Si vous voulez que le monde soit un lieu booléen à deux valeurs, vous devez éviter le cas nul (troisième valeur) vous-même.

N'écrivez pas de clauses IN qui autorisent des valeurs NULL dans la liste. Filtrez-les!

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

Answer 3

Table1 ou Table2 a des valeurs NULL pour common_id. Utilisez cette requête à la place:

 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 4

Juste à côté de ma tête...

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

J'ai couru quelques tests et ici mes résultats.w.r.t. @patmortech la réponse de @rexem commentaires.

Si Table1 ou Table2 n'est pas indexée sur commonID, vous obtenez une analyse de la table, mais @patmortech de la requête est encore deux fois plus rapide (pour un 100K ligne de la table de maître).

Si ni sont indexés sur commonID, vous obtenez deux analyses de la table et la différence est négligeable.

Si les deux sont indexés sur commonID, le "n'existe pas" requête s'exécute dans 1/3 du temps.

Answer 5

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