Pregunta Join vs. sub-query


Soy un usuario de MySQL de la vieja escuela y siempre he preferido JOIN sobre sub-consulta. Pero hoy en día todo el mundo usa la subconsulta y la odio, no sé por qué.

Me falta el conocimiento teórico para juzgar por mí mismo si hay alguna diferencia. Es una subconsulta tan buena como una JOIN y por lo tanto, no hay nada de qué preocuparse?


631
2018-04-05 06:22


origen


Respuestas:


Tomado del manual de MySQL (13.2.10.11 Reescribir subconsultas como combinaciones)

Un JOINT [EXTERIOR] JOIN puede ser más rápido que una subconsulta equivalente porque el servidor podría optimizarlo mejor, un hecho que no es específico solo del servidor MySQL.

Así que las subconsultas pueden ser más lentas que IZQUIERDA [EXTERIOR] SE UNE, pero en mi opinión su fuerza es un poco más legible.


98
2017-09-20 08:09



Las subconsultas son la forma lógica correcta de resolver problemas de la forma "Obtener datos de A, condicional a hechos de B". En tales casos, tiene más sentido lógico pegar B en una sub consulta que hacer una unión. También es más seguro, en un sentido práctico, ya que no tiene que ser cauteloso para obtener hechos duplicados de A debido a múltiples coincidencias contra B.

En términos prácticos, sin embargo, la respuesta generalmente se reduce al rendimiento. Algunos optimizadores chupan limones cuando se les da una combinación frente a una subconsulta, y algunos chupan limones a la inversa, y esto es específico del optimizador, específico de la versión DBMS y específico de la consulta.

Históricamente, las combinaciones explícitas generalmente ganan, por lo tanto, la sabiduría establecida de que las combinaciones son mejores, pero los optimizadores están mejorando todo el tiempo, por lo que prefiero escribir primero las consultas de una manera lógicamente coherente y luego reestructurar si las limitaciones de rendimiento lo justifican.


695
2018-04-05 06:26



En la mayoría de los casos JOINs son más rápidos que las sub-consultas y es muy raro que una sub consulta sea más rápida.

En JOINs RDBMS puede crear un plan de ejecución que sea mejor para su consulta y puede predecir qué datos se deben cargar para procesar y ahorrar tiempo, a diferencia de la subconsulta en la que ejecutará todas las consultas y cargará todos sus datos para realizar el procesamiento.

Lo bueno de las subconsultas es que son más legibles que JOINs: es por eso que la mayoría de las nuevas personas SQL los prefieren; es la manera fácil; pero cuando se trata de rendimiento, las UNIONES son mejores en la mayoría de los casos, aunque no son difíciles de leer también.


330
2018-04-05 06:39



Use EXPLAIN para ver cómo su base de datos ejecuta la consulta en sus datos. Hay un gran "depende" en esta respuesta ...

PostgreSQL puede reescribir una subconsulta a una unión o unirse a una subconsulta cuando cree que una es más rápida que la otra. Todo depende de los datos, índices, correlación, cantidad de datos, consulta, etc.


114
2018-04-05 07:37



En primer lugar, para comparar los dos primeros, debe distinguir las consultas con subconsultas a:

  1. una clase de subconsultas que siempre tiene una consulta equivalente equivalente escrita con combinaciones
  2. una clase de subconsultas que no pueden ser reescritas usando combinaciones

Para la primera clase de consultas, un buen RDBMS considerará que las uniones y subconsultas son equivalentes y producirá los mismos planes de consulta.

En estos días, incluso mysql hace eso.

Aún así, a veces no es así, pero esto no significa que las uniones siempre ganarán. Tuve casos al usar subconsultas en el rendimiento mejorado de MySQL. (Por ejemplo, si hay algo que impide que el planificador mysql calcule correctamente el costo y si el planificador no ve la variante de combinación y la variante de subconsulta como las mismas, entonces las subconsultas pueden superar las uniones forzando una ruta determinada).

La conclusión es que debe probar sus consultas para las variantes de combinación y subconsulta si quiere asegurarse de cuál funcionará mejor.

Para la segunda clase la comparación no tiene sentido ya que esas consultas no se pueden reescribir utilizando combinaciones y, en estos casos, las subconsultas son una forma natural de realizar las tareas requeridas y no debe discriminarlas.


39
2018-05-28 09:33



La documentación de MSDN para SQL Server dice 

Muchas declaraciones de Transact-SQL que incluyen subconsultas pueden formularse alternativamente como combinaciones. Se pueden formular otras preguntas solo con subconsultas. En Transact-SQL, generalmente no hay diferencia de rendimiento entre una instrucción que incluye una subconsulta y una versión semánticamente equivalente que no lo hace. Sin embargo, en algunos casos donde debe verificarse la existencia, una unión produce un mejor rendimiento. De lo contrario, la consulta anidada se debe procesar para cada resultado de la consulta externa para garantizar la eliminación de duplicados. En tales casos, un enfoque de unión arrojaría mejores resultados.

así que si necesitas algo como

select * from t1 where exists select * from t2 where t2.parent=t1.id

intente utilizar join en su lugar. En otros casos, no hace diferencia.

Yo digo: Creando funciones para las subconsultas elimina el problema de cluttter y le permite implementar lógica adicional a las subconsultas. Así que recomiendo crear funciones para subconsultas siempre que sea posible.

El desorden en el código es un gran problema y la industria ha estado trabajando para evitarlo durante décadas.


22
2017-11-16 09:50



Creo que lo que se ha subestimado en las respuestas citadas es la cuestión de duplicados y resultados problemáticos que pueden surgir de casos específicos (de uso).

(aunque Marcelo Cantos sí lo menciona)

Citaré el ejemplo de los cursos Lagunita de Stanford sobre SQL.

Tabla de estudiantes

+------+--------+------+--------+
| sID  | sName  | GPA  | sizeHS |
+------+--------+------+--------+
|  123 | Amy    |  3.9 |   1000 |
|  234 | Bob    |  3.6 |   1500 |
|  345 | Craig  |  3.5 |    500 |
|  456 | Doris  |  3.9 |   1000 |
|  567 | Edward |  2.9 |   2000 |
|  678 | Fay    |  3.8 |    200 |
|  789 | Gary   |  3.4 |    800 |
|  987 | Helen  |  3.7 |    800 |
|  876 | Irene  |  3.9 |    400 |
|  765 | Jay    |  2.9 |   1500 |
|  654 | Amy    |  3.9 |   1000 |
|  543 | Craig  |  3.4 |   2000 |
+------+--------+------+--------+

Aplicar tabla

(solicitudes hechas a universidades específicas y mayores)

+------+----------+----------------+----------+
| sID  | cName    | major          | decision |
+------+----------+----------------+----------+
|  123 | Stanford | CS             | Y        |
|  123 | Stanford | EE             | N        |
|  123 | Berkeley | CS             | Y        |
|  123 | Cornell  | EE             | Y        |
|  234 | Berkeley | biology        | N        |
|  345 | MIT      | bioengineering | Y        |
|  345 | Cornell  | bioengineering | N        |
|  345 | Cornell  | CS             | Y        |
|  345 | Cornell  | EE             | N        |
|  678 | Stanford | history        | Y        |
|  987 | Stanford | CS             | Y        |
|  987 | Berkeley | CS             | Y        |
|  876 | Stanford | CS             | N        |
|  876 | MIT      | biology        | Y        |
|  876 | MIT      | marine biology | N        |
|  765 | Stanford | history        | Y        |
|  765 | Cornell  | history        | N        |
|  765 | Cornell  | psychology     | Y        |
|  543 | MIT      | CS             | N        |
+------+----------+----------------+----------+

Tratemos de encontrar los puntajes de GPA para los estudiantes que se han postulado a CS mayor (independientemente de la universidad)

Usando una subconsulta:

select GPA from Student where sID in (select sID from Apply where major = 'CS');

+------+
| GPA  |
+------+
|  3.9 |
|  3.5 |
|  3.7 |
|  3.9 |
|  3.4 |
+------+

El valor promedio para este conjunto de resultados es:

select avg(GPA) from Student where sID in (select sID from Apply where major = 'CS');

+--------------------+
| avg(GPA)           |
+--------------------+
| 3.6800000000000006 |
+--------------------+

Usando una unión:

select GPA from Student, Apply where Student.sID = Apply.sID and Apply.major = 'CS';

+------+
| GPA  |
+------+
|  3.9 |
|  3.9 |
|  3.5 |
|  3.7 |
|  3.7 |
|  3.9 |
|  3.4 |
+------+

valor promedio para este conjunto de resultados:

select avg(GPA) from Student, Apply where Student.sID = Apply.sID and Apply.major = 'CS';

+-------------------+
| avg(GPA)          |
+-------------------+
| 3.714285714285714 |
+-------------------+

Es obvio que el segundo intento arroja resultados engañosos en nuestro caso de uso, dado que cuenta duplicados para el cálculo del valor promedio. También es evidente que el uso de distinct con la declaración basada en la unión se no eliminar el problema, dado que erróneamente mantendrá una de cada tres apariciones del 3.9 Puntuación. El caso correcto es contabilizar DOS (2) ocurrencias de 3.9puntuación dado que en realidad tenemos DOS (2) estudiantes con ese puntaje que cumplen con nuestros criterios de consulta.

Parece que en algunos casos una subconsulta es la forma más segura de hacerlo, además de cualquier problema de rendimiento.


19
2017-10-19 10:05



Ejecutar en una base de datos muy grande de un viejo Mambo CMS:

SELECT id, alias
FROM
  mos_categories
WHERE
  id IN (
    SELECT
      DISTINCT catid
    FROM mos_content
  );

0 segundos

SELECT
  DISTINCT mos_content.catid,
  mos_categories.alias
FROM
  mos_content, mos_categories
WHERE
  mos_content.catid = mos_categories.id;

~ 3 segundos

Una EXPLAIN muestra que examinan exactamente la misma cantidad de filas, pero una demora 3 segundos y la otra es casi instantánea. ¿Moraleja de la historia? Si el rendimiento es importante (¿cuándo no?), Pruébelo de múltiples maneras y vea cuál es el más rápido.

Y...

SELECT
  DISTINCT mos_categories.id,
  mos_categories.alias
FROM
  mos_content, mos_categories
WHERE
  mos_content.catid = mos_categories.id;

0 segundos

De nuevo, los mismos resultados, el mismo número de filas examinadas. Supongo que DISTINCT mos_content.catid tarda mucho más en descubrir que DISTINCT mos_categories.id sí lo hace.


15
2017-10-20 22:27



Versión de MySQL: 5.5.28-0ubuntu0.12.04.2-log

También tuve la impresión de que JOIN siempre es mejor que una subconsulta en MySQL, pero EXPLAIN es una mejor manera de emitir un juicio. Aquí hay un ejemplo donde las consultas secundarias funcionan mejor que JOINs.

Aquí está mi consulta con 3 sub-consultas:

EXPLAIN SELECT vrl.list_id,vrl.ontology_id,vrl.position,l.name AS list_name, vrlih.position AS previous_position, vrl.moved_date 
FROM `vote-ranked-listory` vrl 
INNER JOIN lists l ON l.list_id = vrl.list_id 
INNER JOIN `vote-ranked-list-item-history` vrlih ON vrl.list_id = vrlih.list_id AND vrl.ontology_id=vrlih.ontology_id AND vrlih.type='PREVIOUS_POSITION' 
INNER JOIN list_burial_state lbs ON lbs.list_id = vrl.list_id AND lbs.burial_score < 0.5 
WHERE vrl.position <= 15 AND l.status='ACTIVE' AND l.is_public=1 AND vrl.ontology_id < 1000000000 
 AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=43) IS NULL 
 AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=55) IS NULL 
 AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=246403) IS NOT NULL 
ORDER BY vrl.moved_date DESC LIMIT 200;

EXPLAIN muestra:

+----+--------------------+----------+--------+-----------------------------------------------------+--------------+---------+-------------------------------------------------+------+--------------------------+
| id | select_type        | table    | type   | possible_keys                                       | key          | key_len | ref                                             | rows | Extra                    |
+----+--------------------+----------+--------+-----------------------------------------------------+--------------+---------+-------------------------------------------------+------+--------------------------+
|  1 | PRIMARY            | vrl      | index  | PRIMARY                                             | moved_date   | 8       | NULL                                            |  200 | Using where              |
|  1 | PRIMARY            | l        | eq_ref | PRIMARY,status,ispublic,idx_lookup,is_public_status | PRIMARY      | 4       | ranker.vrl.list_id                              |    1 | Using where              |
|  1 | PRIMARY            | vrlih    | eq_ref | PRIMARY                                             | PRIMARY      | 9       | ranker.vrl.list_id,ranker.vrl.ontology_id,const |    1 | Using where              |
|  1 | PRIMARY            | lbs      | eq_ref | PRIMARY,idx_list_burial_state,burial_score          | PRIMARY      | 4       | ranker.vrl.list_id                              |    1 | Using where              |
|  4 | DEPENDENT SUBQUERY | list_tag | ref    | list_tag_key,list_id,tag_id                         | list_tag_key | 9       | ranker.l.list_id,const                          |    1 | Using where; Using index |
|  3 | DEPENDENT SUBQUERY | list_tag | ref    | list_tag_key,list_id,tag_id                         | list_tag_key | 9       | ranker.l.list_id,const                          |    1 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | list_tag | ref    | list_tag_key,list_id,tag_id                         | list_tag_key | 9       | ranker.l.list_id,const                          |    1 | Using where; Using index |
+----+--------------------+----------+--------+-----------------------------------------------------+--------------+---------+-------------------------------------------------+------+--------------------------+

La misma consulta con JOINs es:

EXPLAIN SELECT vrl.list_id,vrl.ontology_id,vrl.position,l.name AS list_name, vrlih.position AS previous_position, vrl.moved_date 
FROM `vote-ranked-listory` vrl 
INNER JOIN lists l ON l.list_id = vrl.list_id 
INNER JOIN `vote-ranked-list-item-history` vrlih ON vrl.list_id = vrlih.list_id AND vrl.ontology_id=vrlih.ontology_id AND vrlih.type='PREVIOUS_POSITION' 
INNER JOIN list_burial_state lbs ON lbs.list_id = vrl.list_id AND lbs.burial_score < 0.5 
LEFT JOIN list_tag lt1 ON lt1.list_id = vrl.list_id AND lt1.tag_id = 43 
LEFT JOIN list_tag lt2 ON lt2.list_id = vrl.list_id AND lt2.tag_id = 55 
INNER JOIN list_tag lt3 ON lt3.list_id = vrl.list_id AND lt3.tag_id = 246403 
WHERE vrl.position <= 15 AND l.status='ACTIVE' AND l.is_public=1 AND vrl.ontology_id < 1000000000 
AND lt1.list_id IS NULL AND lt2.tag_id IS NULL 
ORDER BY vrl.moved_date DESC LIMIT 200;

y el resultado es:

+----+-------------+-------+--------+-----------------------------------------------------+--------------+---------+---------------------------------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                                       | key          | key_len | ref                                         | rows | Extra                                        |
+----+-------------+-------+--------+-----------------------------------------------------+--------------+---------+---------------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | lt3   | ref    | list_tag_key,list_id,tag_id                         | tag_id       | 5       | const                                       | 2386 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | l     | eq_ref | PRIMARY,status,ispublic,idx_lookup,is_public_status | PRIMARY      | 4       | ranker.lt3.list_id                          |    1 | Using where                                  |
|  1 | SIMPLE      | vrlih | ref    | PRIMARY                                             | PRIMARY      | 4       | ranker.lt3.list_id                          |  103 | Using where                                  |
|  1 | SIMPLE      | vrl   | ref    | PRIMARY                                             | PRIMARY      | 8       | ranker.lt3.list_id,ranker.vrlih.ontology_id |   65 | Using where                                  |
|  1 | SIMPLE      | lt1   | ref    | list_tag_key,list_id,tag_id                         | list_tag_key | 9       | ranker.lt3.list_id,const                    |    1 | Using where; Using index; Not exists         |
|  1 | SIMPLE      | lbs   | eq_ref | PRIMARY,idx_list_burial_state,burial_score          | PRIMARY      | 4       | ranker.vrl.list_id                          |    1 | Using where                                  |
|  1 | SIMPLE      | lt2   | ref    | list_tag_key,list_id,tag_id                         | list_tag_key | 9       | ranker.lt3.list_id,const                    |    1 | Using where; Using index                     |
+----+-------------+-------+--------+-----------------------------------------------------+--------------+---------+---------------------------------------------+------+----------------------------------------------+

Una comparación de rows columna dice la diferencia y la consulta con JOINs está usando Using temporary; Using filesort.

Por supuesto, cuando ejecuto ambas consultas, la primera se realiza en 0.02 segundos, la segunda no termina incluso después de 1 minuto, por lo que EXPLAIN explicó estas consultas correctamente.

Si no tengo INNER JOIN en el list_tag tabla, es decir, si elimino

AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=246403) IS NOT NULL  

desde la primera consulta y correspondientemente:

INNER JOIN list_tag lt3 ON lt3.list_id = vrl.list_id AND lt3.tag_id = 246403

desde la segunda consulta, EXPLAIN devuelve el mismo número de filas para ambas consultas y ambas consultas se ejecutan igualmente rápido.


11
2018-06-06 18:25



Las subconsultas tienen la capacidad de calcular funciones de agregación en una mosca. P.ej. Encuentre el precio mínimo del libro y obtenga todos los libros que se venden con este precio. 1) Usar subconsultas:

SELECT titles, price
FROM Books, Orders
WHERE price = 
(SELECT MIN(price)
 FROM Orders) AND (Books.ID=Orders.ID);

2) usando JOINs

SELECT MIN(price)
     FROM Orders;
-----------------
2.99

SELECT titles, price
FROM Books b
INNER JOIN  Orders o
ON b.ID = o.ID
WHERE o.price = 2.99;

11
2018-06-17 07:24



Las subconsultas generalmente se utilizan para devolver una sola fila como un valor atómico, aunque se pueden usar para comparar valores contra varias filas con la palabra clave IN. Están permitidos en casi cualquier punto significativo en una declaración de SQL, incluida la lista de objetivos, la cláusula WHERE, y así sucesivamente. Una subconsulta simple podría usarse como una condición de búsqueda. Por ejemplo, entre un par de tablas:

   SELECT title FROM books WHERE author_id = (SELECT id FROM authors WHERE last_name = 'Bar' AND first_name = 'Foo');

Tenga en cuenta que usar un operador de valor normal en los resultados de una subconsulta requiere que solo se devuelva un campo. Si está interesado en verificar la existencia de un valor único dentro de un conjunto de otros valores, use IN:

   SELECT title FROM books WHERE author_id IN (SELECT id FROM authors WHERE last_name ~ '^[A-E]');

Esto es obviamente diferente de, por ejemplo, un LEFT-JOIN en el que solo quieres unir cosas de la tabla A y B incluso si la condición de unión no encuentra ningún registro coincidente en la tabla B, etc.

Si solo te preocupa la velocidad, deberás consultar con tu base de datos y escribir una buena consulta para ver si hay alguna diferencia significativa en el rendimiento.


11
2018-04-05 06:34