diferencia - mysql workbench

Consulta "NO ENCENDIDA" de MySQL

Question

Quería ejecutar una consulta simple para arrojar todas las filas de la Table1 donde el valor de la columna principal no está presente en una columna en otra tabla (Tabla Table2 ).

Intenté usar:

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

En su lugar, arroja un error de sintaxis. La búsqueda de Google me llevó a foros en los que la gente decía que MySQL no es compatible con NOT IN y que se debe utilizar algo extremadamente complejo. ¿Es esto cierto? ¿O estoy cometiendo un error horrendo?

Answer 1

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

MySQL, así como todos los demás sistemas, excepto SQL Server, puede optimizar LEFT JOIN / IS NULL para devolver FALSE tan pronto se encuentre el valor coincidente, y es el único sistema que se preocupó por documentar este comportamiento. [...] Ya que MySQL no es capaz de usar los algoritmos HASH y MERGE join, el único ANTI JOIN que es capaz de hacer es NESTED LOOPS ANTI JOIN

[...]

Esencialmente, [ NOT IN ] es exactamente el mismo plan que usa LEFT JOIN / IS NULL , a pesar de que estos planes son ejecutados por las diferentes ramas de código y se ven diferentes en los resultados de EXPLAIN . De hecho, los algoritmos son los mismos y las consultas se completan al mismo tiempo.

[...]

Es difícil decir la razón exacta de [caída de rendimiento cuando se usa NOT EXISTS ] , ya que esta caída es lineal y no parece depender de la distribución de datos, el número de valores en ambas tablas, etc., siempre que ambos campos estén indexados. Como hay tres códigos en MySQL que básicamente hacen un trabajo, es posible que el código responsable de EXISTS realice algún tipo de verificación adicional que requiera tiempo adicional.

[...]

MySQL puede optimizar los tres métodos para hacer una especie de NESTED LOOPS ANTI JOIN . [...] Sin embargo, estos tres métodos generan tres planes diferentes que se ejecutan mediante tres piezas de código diferentes. El código que ejecuta el predicado EXISTS es aproximadamente un 30% menos eficiente [...]

Es por eso que la mejor forma de buscar los valores perdidos en MySQL es usar un LEFT JOIN / IS NULL o NOT IN lugar de NOT EXISTS .

(énfasis añadido)

Answer 2

Desafortunadamente, parece ser un problema con el uso de MySql de la cláusula "NO ENCENDIDO", la captura de pantalla a continuación muestra la opción de sub-consulta que arroja resultados incorrectos:

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>

Answer 3

La opción de subconsulta ya se ha respondido, pero tenga en cuenta que, en muchos casos, una LEFT JOIN puede ser una forma más rápida de hacerlo:

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

Si desea verificar varias tablas para asegurarse de que no estén presentes en ninguna de las tablas (como en el comentario de SRKR), puede usar esto:

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

Answer 4

Para usar IN, debe tener un conjunto, use esta sintaxis en su lugar:

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

Answer 5

Tenga cuidado, NOT IN no es un alias para <> ANY , pero para <> ALL ¡ <> 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

no puede ser reemplazado por

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

Debes usar

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