Zapytanie MySQL „NOT IN”

Chciałem uruchomić proste zapytanie, aby wyrzucić wszystkie wiersze, w Table1których wartość kolumny głównej nie występuje w kolumnie w innej tabeli ( Table2).

Próbowałem użyć:

SELECT * FROM Table1 WHERE Table1.principal NOT IN Table2.principal

Zamiast tego generuje błąd składniowy. Wyszukiwarka Google zaprowadziła mnie na fora, na których ludzie mówili, że MySQL nie obsługuje NOT INi należy zastosować coś niezwykle złożonego. Czy to prawda? Czy popełniam straszny błąd?

Aby użyć IN, musisz mieć zestaw, zamiast tego użyj tej składni:

SELECT * FROM Table1 WHERE Table1.principal NOT IN (SELECT principal FROM table2)

Odpowiedź na pytanie dotyczące podpowiedzi została już udzielona, ​​ale należy pamiętać, że w wielu przypadkach LEFT JOINmożna to zrobić szybciej:

SELECT table1.*
FROM table1 LEFT JOIN table2 ON table2.principal=table1.principal
WHERE table2.principal IS NULL

Jeśli chcesz sprawdzić wiele tabel, aby upewnić się, że nie ma ich w żadnej z tabel (jak w komentarzu SRKR), możesz użyć tego:

SELECT table1.*
FROM table1
LEFT JOIN table2 ON table2.name=table1.name
LEFT JOIN table3 ON table3.name=table1.name
WHERE table2.name IS NULL AND table3.name IS NULL

NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL w MySQL

MySQL, podobnie jak wszystkie inne systemy oprócz SQL Server, jest w stanie zoptymalizować LEFT JOIN/IS NULL zwrócić, FALSEgdy tylko zostanie znaleziona pasująca wartość, i jest to jedyny system, który zechciał udokumentować to zachowanie. […] Ponieważ MySQL nie jest w stanie używać algorytmów HASHi MERGEłączyć się, jedyne, ANTI JOINco potrafi, toNESTED LOOPS ANTI JOIN

[…]

Zasadniczo [ NOT IN] jest dokładnie tym samym planem, z którego LEFT JOIN/ IS NULLkorzysta, mimo że plany te są wykonywane przez różne gałęzie kodu i wyglądają inaczej w wynikach EXPLAIN. Algorytmy są w rzeczywistości takie same, a zapytania kończą się w tym samym czasie.

[…]

Trudno jest podać dokładny powód [spadku wydajności podczas używania NOT EXISTS] , ponieważ spadek ten jest liniowy i nie wydaje się zależeć od rozkładu danych, liczby wartości w obu tabelach itp., O ile oba pola są indeksowane. Ponieważ w MySQL są trzy fragmenty kodu, które w zasadzie wykonują jedno zadanie, możliwe jest, że kod odpowiedzialny za wykonanie EXISTSpewnego rodzaju dodatkowej kontroli zajmuje więcej czasu.

[…]

MySQL może zoptymalizować wszystkie trzy metody, aby zrobić coś w rodzaju NESTED LOOPS ANTI JOIN. […] Jednak te trzy metody generują trzy różne plany, które są wykonywane przez trzy różne fragmenty kodu. Kod, który wykonuje EXISTSpredykat, jest o około 30% mniej wydajny […]

Dlatego najlepszym sposobem wyszukiwania brakujących wartości w MySQL jest użycie LEFT JOIN/ IS NULLlub NOT INzamiast NOT EXISTS.

(podkreślenia dodane)

Niestety wydaje się, że jest to problem ze stosowaniem przez MySql klauzuli „NOT IN”, zrzut ekranu poniżej pokazuje opcję zapytania podrzędnego zwracającą błędne wyniki:

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 1.1.8                        |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.5.21                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
7 rows in set (0.07 sec)

mysql> select count(*) from TABLE_A where TABLE_A.Pkey not in (select distinct TABLE_B.Fkey from TABLE_B );
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.07 sec)

mysql> select count(*) from TABLE_A left join TABLE_B on TABLE_A.Pkey = TABLE_B.Fkey where TABLE_B.Pkey is null;
+----------+
| count(*) |
+----------+
|      139 |
+----------+
1 row in set (0.06 sec)

mysql> select count(*) from TABLE_A where NOT EXISTS (select * FROM TABLE_B WHERE TABLE_B.Fkey = TABLE_A.Pkey );
+----------+
| count(*) |
+----------+
|      139 |
+----------+
1 row in set (0.06 sec)

mysql> 

Uważaj NOT INnie jest pseudonimem <> ANY, ale dla <> ALL!

http://dev.mysql.com/doc/refman/5.0/en/any-in-some-subqueries.html

SELECT c FROM t1 LEFT JOIN t2 USING (c) WHERE t2.c IS NULL

nie mogę zostać zastąpiony przez

SELECT c FROM t1 WHERE c NOT IN (SELECT c FROM t2)

Musisz użyć

SELECT c FROM t1 WHERE c <> ANY (SELECT c FROM t2)