PostgreSQL „NOT IN” i podzapytanie

Próbuję wykonać to zapytanie:

SELECT mac, creation_date 
FROM logs 
WHERE logs_type_id=11
AND mac NOT IN (select consols.mac from consols)

Ale nie mam żadnych wyników. Przetestowałem to i wiem, że jest coś nie tak ze składnią. W MySQL takie zapytanie działa doskonale. Dodałem wiersz, aby upewnić się, że istnieje taki, macktórego nie ma w consolstabeli, ale nadal nie daje żadnych wyników.

postgresql subquery

— skowron-line
źródło

Czy consols.mackolumna jest NULLczy NOT NULL?

— Mark Byers,

Odpowiedzi:

167

Korzystając z opcji NOT IN, należy upewnić się, że żadna z wartości nie jest NULL:

SELECT mac, creation_date 
FROM logs 
WHERE logs_type_id=11
AND mac NOT IN (
    SELECT mac
    FROM consols
    WHERE mac IS NOT NULL -- add this
)

— Mark Byers
źródło

Uwaga: WHERE mac IS NOT NULLklauzula w podzapytaniu nie jest potrzebna, ponieważ In(...)zawsze usuwa wartości NULL (i duplikaty). Ponieważ zestaw nie może zawierać wartości NULL

— wildplasser

@wildplasser Nie wiem o tym. Nie działało to dla mnie, dopóki nie dodałem IS NOT NULL. Zagnieżdżony SELECTzwracał kilka NULLS, a to było potknięcie się IN(SELECT...).

— robins35

Byłbym bardzo wdzięczny za wyjaśnienie, dlaczego IS NOT NULLto działa.

— mbarkhau

Wydaje się, że użycie NULLw NOT INklauzuli nie działa, ponieważ porównanie do NULLnie jest ani prawdziwe, ani fałszywe. sqlbadpractices.com/using-not-in-operator-with-null-values

— mbarkhau

Zapytanie nie zwróci żadnych wierszy w przypadku braku zapytania, is not nulljeśli podzapytanie nie zwróci żadnych pasujących wartości i co najmniej jednej nullwartości. Z sekcji 9.22 bieżącego podręcznika PostgreSQL (wersja 10): „[…] jeśli nie ma równych wartości po prawej stronie i co najmniej jeden wiersz po prawej stronie daje wartość null, wynik konstrukcji NOT IN będzie miał wartość NULL, nie prawda ”.

— Christopher Lewis

Korzystając z opcji NOT IN, należy również rozważyć opcję NOT EXISTS, która po cichu obsługuje przypadki zerowe. Zobacz także PostgreSQL Wiki

SELECT mac, creation_date 
FROM logs lo
WHERE logs_type_id=11
AND NOT EXISTS (
  SELECT *
  FROM consols nx
  WHERE nx.mac = lo.mac
  );

— wildplasser
źródło

Zwróć także uwagę na ogromną utratę wydajności podczas korzystania z NOT EXISTSvs... NOT IN

— IcanDivideBy0

@ IcanDivideBy0 W większości przypadków generują ten sam plan zapytań. Czy to przetestowałeś?

— wildplasser

W przypadku podzapytań użycie opcji NIE W zamiast NIE ISTNIEJE również zwiększa wydajność. Zobacz wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_NOT_IN

— Gerbrand

Możesz również użyć warunku LEFT JOIN i IS NULL:

SELECT 
  mac, 
  creation_date 
FROM 
  logs
    LEFT JOIN consols ON logs.mac = consols.mac
WHERE 
  logs_type_id=11
AND
  consols.mac IS NULL;

Indeks w kolumnach „mac” może poprawić wydajność.

— Frank Heikens
źródło