SQL “select lorsqu'elle n'est pas sous-requête” ne retourne aucun résultat

Mise à jour:

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

• PAS au lieu de n'EXISTE PAS au lieu de LEFT JOIN /NULL: SQL Server
• PAS au lieu de n'EXISTE PAS au lieu de LEFT JOIN /NULL: PostgreSQL
• PAS au lieu de n'EXISTE PAS au lieu de LEFT JOIN /NULL: Oracle
• PAS au lieu de n'EXISTE PAS au lieu de LEFT JOIN /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
          )

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

Quand il est nullable, NOT IN est différente, puisque IN (et, par conséquent, NOT IN) retour 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 valeur les rendements de la NULL résultat qui rend la totalité du résultat NULL trop.

Nous n'avons jamais ne peut pas dire certainement que 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 (car il sera toujours correspondre à la FALSE ou NULL).

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

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

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

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

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)

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

Supposons que ces valeurs pour common_id:

Common - 1
Table1 - 2
Table2 - 3, null

Nous voulons la ligne dans la Commune de revenir, car il n'existe pas dans les autres tables. Toutefois, la valeur null jette dans un monkey wrench.

Avec ces valeurs, la requête est équivalente à:

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

Qui est équivalent à:

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

C'est là que le problème commence. Lors de la comparaison avec une valeur null, la réponse est inconnue. Ainsi la requête réduit à

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

faux ou inconnu inconnu:

select *
from Common
where true
and not (unknown)

vrai et pas inconnu est également inconnu:

select *
from Common
where unknown

Où la condition de ne pas renvoyer des enregistrements où le résultat est inconnu, nous obtenons donc pas de documents.

Une façon de traiter cette question est d'utiliser le existe opérateur plutôt que dans. Existe ne retourne jamais inconnu parce qu'il fonctionne sur des lignes plutôt que des colonnes. (Une ligne existe ou qu'il n'en est rien; rien de tout cela nulle ambiguïté au niveau de la ligne!)

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)

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