SQL "select where not in subquery" nie zwraca żadnych wyników
Zastrzeżenie: rozgryzłem problem( myślę), ale chciałem dodać ten problem do Stack Overflow, ponieważ nie mogłem (łatwo) znaleźć go nigdzie. Poza tym, ktoś może mieć lepszą odpowiedź niż ja.
Mam bazę danych, w której jedna tabela "wspólna" odnosi się do kilku innych tabel. Chciałem zobaczyć, jakie rekordy w wspólnej tabeli zostały osierocone (tzn. nie miały odniesień z żadnej z innych tabel).
Uruchomiłem to zapytanie:
select *
from Common
where common_id not in (select common_id from Table1)
and common_id not in (select common_id from Table2)
I wiedz, że są osierocone zapisy, ale nie zostały zwrócone. Dlaczego nie?
(to jest SQL Server, jeśli ma to znaczenie.)
11 answers
Update:
Te artykuły na moim blogu opisują różnice między metodami bardziej szczegółowo:
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
Istnieją trzy sposoby na takie zapytanie:
-
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 )
Gdy {[24] } nie jest nullable, wszystkie te zapytania są semantycznie takie same.
Gdy wartość jest nullable, NOT IN
jest inna, ponieważ IN
(a zatem NOT IN
) zwraca NULL
gdy wartość nie pasuje do niczego z listy zawierającej NULL
.
Może to być mylące, ale może stać się bardziej oczywiste, jeśli przypomnimy sobie składnię alternatywną za to:
common_id = ANY
(
SELECT common_id
FROM table1 t1
)
Wynik tego warunku jest iloczynem logicznym wszystkich porównań w obrębie listy. Oczywiście, pojedyncza wartość NULL
daje wynik NULL
, który renderuje cały wynik NULL
.
Nigdy nie możemy powiedzieć zdecydowanie, że common_id
nie jest równa niczym z tej listy, ponieważ przynajmniej jedna z wartości to NULL
.
Załóżmy, że mamy te dane:
common
--
1
3
table1
--
NULL
1
2
LEFT JOIN / IS NULL
and NOT EXISTS
will return 3
, NOT IN
zwróci nic (ponieważ zawsze będzie oceniać do FALSE
lub NULL
).
In MySQL
, in case on non-nullable column, LEFT JOIN / IS NULL
and NOT IN
are a little bit (several percent) more efficient than NOT EXISTS
. Jeśli kolumna jest nullable, NOT EXISTS
jest najbardziej efektywna(ponownie, niewiele).
W Oracle
, wszystkie trzy zapytania dają takie same plany(an ANTI JOIN
).
W SQL Server
, NOT IN
/ NOT EXISTS
są bardziej wydajne, ponieważ {[7] } nie można zoptymalizować do ANTI JOIN
przez jego optymalizator.
W PostgreSQL
, LEFT JOIN / IS NULL
oraz W przeciwieństwie do innych typów plików, które nie są w pełni funkcjonalne, nie są w pełni funkcjonalne i nie są w pełni funkcjonalne.]}
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-09-19 21:46:09
Jeśli chcesz, aby świat był miejscem logicznym o dwóch wartościach, musisz samemu zapobiec Przypadkowi null (trzeciej wartości).
Nie pisz w klauzulach zezwalających na null po stronie listy. Odfiltruj ich!
common_id not in
(
select common_id from Table1
where common_id is not null
)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-09-10 17:29:24
Table1 lub Table2 mają pewne wartości null dla common_id. Zamiast tego użyj tego zapytania:
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)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-09-10 16:08:35
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)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-09-10 16:37:05
Tylko z czubka mojej głowy...
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
Przeprowadziłem kilka testów i oto moje wyniki w. R. T. odpowiedź @patmortech i komentarze @rexem.
Jeśli Table1 lub Table2 nie są indeksowane na commonID, otrzymasz skanowanie tabeli, ale zapytanie @patmortech jest nadal dwa razy szybsze (dla tabeli głównej 100k wierszy).
Jeśli żadne z nich nie są indeksowane na commonID, otrzymujesz dwa skany tabeli, a różnica jest znikoma.
Jeśli oba są indeksowane na commonID, zapytanie" not exists " działa w 1/3 czas.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-09-10 18:31:33
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-09-10 16:25:36
Załóżmy, że te wartości dla common_id:
Common - 1
Table1 - 2
Table2 - 3, null
Chcemy, aby wspólny wiersz wrócił, ponieważ nie istnieje w żadnej z pozostałych tabel. Jednak null rzuca klucz do małpy.
Z tymi wartościami zapytanie jest równoważne:
select *
from Common
where 1 not in (2)
and 1 not in (3, null)
To jest równoważne:
select *
from Common
where not (1=2)
and not (1=3 or 1=null)
Tu zaczyna się problem. W porównaniu z null, odpowiedź jest nieznana . Więc zapytanie redukuje się do
select *
from Common
where not (false)
and not (false or unkown)
False or unknown is unknown:
select *
from Common
where true
and not (unknown)
True I not unkown is also unkown:
select *
from Common
where unknown
Warunek gdzie nie zwraca rekordów, w których wynik jest nieczytelny, więc nie otrzymujemy żadnych rekordów z powrotem.
Jednym ze sposobów radzenia sobie z tym jest użycie operatora exists zamiast in. Exists never zwraca unkown, ponieważ operuje na wierszach, a nie kolumnach. (Wiersz albo istnieje, albo nie; żadna z tej niejednoznaczności null na poziomie wiersza!)
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)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-09-11 14:19:40
To mi się udało:)
Select * from Common
Gdzie
Common_id not in (select ISNULL (common_id, 'dummy-data') from Table1)
And common_id not in (select ISNULL (common_id, 'dummy-data') from Table2)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2020-06-20 09:12:55
select *,
(select COUNT(ID) from ProductMaster where ProductMaster.CatID = CategoryMaster.ID) as coun
from CategoryMaster
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-06-10 12:36:21
Miałem przykład, w którym szukałem i ponieważ jedna tabela utrzymywała wartość jako podwójne, a druga jako ciąg znaków, nie pasują (lub nie pasują bez rzutu). Ale tylko nie w . Jako Wybierz ... Do środka ...Zadziałało. Dziwne, ale pomyślałem, że się podzielę na wypadek, gdyby ktoś inny napotkał to proste rozwiązanie.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2019-12-07 12:57:11
Postępuj zgodnie z poniższym przykładem, aby zrozumieć powyższe temat:
Możesz również odwiedzić poniższy link, aby wiedzieć anty join
select department_name,department_id from hr.departments dep
where not exists
(select 1 from hr.employees emp
where emp.department_id=dep.department_id
)
order by dep.department_name;
DEPARTMENT_NAME DEPARTMENT_ID
Benefits 160
Construction 180
Contracting 190
.......
Ale jeśli użyjemy NOT IN
W takim przypadku nie otrzymamy żadnych danych.
select Department_name,department_id from hr.departments dep
where department_id not in (select department_id from hr.employees );
Nie znaleziono danych
Dzieje się tak, ponieważ (select department_id from hr.employees
) Zwraca wartość null, a całe zapytanie jest oceniane jako false. Możemy to zobaczyć, jeśli zmienimy SQL nieco jak poniżej i obsłużymy wartości null za pomocą NVL funkcja.
select Department_name,department_id from hr.departments dep
where department_id not in (select NVL(department_id,0) from hr.employees )
Teraz otrzymujemy dane:
DEPARTMENT_NAME DEPARTMENT_ID
Treasury 120
Corporate Tax 130
Control And Credit 140
Shareholder Services 150
Benefits 160
....
Ponownie otrzymujemy dane, ponieważ obsłużyliśmy wartość null za pomocą funkcji NVL.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2020-05-18 23:19:05