Pesquisa de junção vs. sub-pesquisa

Na maioria dos casos {[[0]}s são mais rápidos que as sub-consultas e é muito raro que uma sub-consulta seja mais rápida.

Em JOINs RDBMS pode criar um plano de execução que é melhor para a sua consulta e pode prever quais os dados que devem ser carregados para ser processado e poupar tempo, ao contrário da sub-consulta onde ele irá executar todas as consultas e carregar todos os seus dados para fazer o processamento.

A coisa boa em sub-consultas é que eles são mais legíveis do que {[[0]} s: é por isso que a maioria das novas pessoas SQL preferem eles; é a maneira fácil; mas quando se trata de desempenho, as juntas são melhores na maioria dos casos, mesmo que não sejam difíceis de ler também.

Use explique para ver como a sua base de dados executa a consulta nos seus dados. Há um enorme "depende" nesta resposta...

O PostgreSQL pode reescrever um subcontingente a uma junção ou a uma junção a uma subcontingente quando pensa que uma é mais rápida do que a outra. Tudo depende dos dados, índices, correlação, quantidade de dados, consulta, etc.

No ano de 2010 eu teria me juntado ao autor destas perguntas e teria votado fortemente em {[[4]}, mas com muito mais experiência (especialmente em MySQL) eu posso afirmar: sim subqueries pode ser melhor. Eu li várias respostas aqui; algumas subqueries declaradas são mais rápidas, mas faltava uma boa explicação. Espero poder dar uma resposta (muito) tardia:

E a segunda declaração importante: a dimensão é importante

Se usar sub-consultas, deverá estar ciente de como o servidor DB executa a sub-consulta. Especialmente se a sub-consulta for avaliada uma vez ou para cada linha! Por outro lado, um DB-Server moderno é capaz de otimizar muito. Em alguns casos, um subquery ajuda a otimizar uma consulta, mas uma versão mais recente do DB-Server pode tornar a otimização obsoleta.

Sub-consultas em campos de selecção

SELECT moo, (SELECT roger FROM wilco WHERE moo = me) AS bar FROM foo

Esteja ciente de que uma sub-consulta é executada para cada linha resultante de foo.
Evite isso, se possível; ele pode reduzir drasticamente sua consulta em grandes conjuntos de dados. No entanto, se a sub-consulta não tem referência a foo, Ela pode ser otimizada pelo DB-server como conteúdo estático e pode ser avaliada apenas uma vez.

Sub-perguntas na Declaração de onde

SELECT moo FROM foo WHERE bar = (SELECT roger FROM wilco WHERE moo = me)

Se tiveres sorte, o DB optimiza isto internamente para um JOIN. Caso contrário, a sua consulta irá torne-se muito, muito lento em grandes conjuntos de dados porque ele irá executar a sub-consulta para cada linha em foo, não apenas os resultados como no select-type.

Sub-questões na Declaração de Adesão

SELECT moo, bar 
  FROM foo 
    LEFT JOIN (
      SELECT MIN(bar), me FROM wilco GROUP BY me
    ) ON moo = me

Sub-consultas aninhadas

SELECT moo, bar
  FROM (
    SELECT moo, CONCAT(roger, wilco) AS bar
      FROM foo
      GROUP BY moo
      HAVING bar LIKE 'SpaceQ%'
  ) AS temp_foo
  ORDER BY bar

Podes fazer ninho em sub-consultas em vários níveis. Isso pode ajudar em grandes conjuntos de dados se você tiver que agrupar ou classificar os resultados. Normalmente, o DB-Server cria uma tabela temporária para isso, mas às vezes você não precisa de ordenação em toda a mesa, apenas no conjunto de resultados. Isto pode proporcionar um desempenho muito melhor dependendo do tamanho da tabela.

Conclusão

As Sub-consultas não substituem um JOIN e não as deve usar assim (embora possível). Na minha humilde opinião, o uso correto de uma sub-consulta é o uso como uma rápida substituição de CREATE TEMPORARY TABLE .... Uma boa sub-consulta reduz um conjunto de dados de uma forma que você não pode realizar numa declaração ON de um JOIN. Se uma sub-consulta tem um de as palavras-chave GROUP BY ou DISTINCT e não se situa preferencialmente nos campos seleccionados ou na indicação de onde, então pode melhorar muito o desempenho.

Em primeiro lugar, para comparar as duas primeiras, deve distinguir as consultas com as sub-consultas de:

1. Uma classe de subqueries que têm sempre a consulta equivalente correspondente escrita com joins
2. Uma classe de sub-séries que não podem ser reescritas usando juntas

Para a primeira classe de consultas, um bom RDBMS verá as ligações e sub-consultas como equivalentes e produzirá os mesmos planos de consulta.

Ainda, às vezes não, mas isso não significa que o joins sempre vai ganhar - eu tive casos quando usando subqueries no desempenho melhorado mysql. (Por exemplo, se há algo que impede o planner mysql de estimar corretamente o custo e se o planejador não vê a variante de junção e subquery como mesmo, então subqueries pode superar as junções forçando um certo caminho).

A conclusão é que deve testar as suas consultas para as variantes de adesão e sub-adesão se quiser ter a certeza qual deles será melhor.

Para a segunda classe a comparação faz sentido, uma vez que essas consultas não pode ser reescrito usando associações e, nestes casos, subconsultas são o caminho natural para fazer as tarefas necessárias e você não deve discriminar contra eles.

Eu acho que o que foi sub-enfatizado nas respostas citadas é a questão de duplicados e resultados problemáticos que podem surgir de casos específicos (de uso).

(embora Marcelo Cantos o mencione)

Tabela Estudantil

+------+--------+------+--------+
| 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 O Quadro

(pedidos apresentados a universidades e a universidades específicas)

+------+----------+----------------+----------+
| 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        |
+------+----------+----------------+----------+

usando um subquery:

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 |
+------+

O valor médio para este conjunto de resultados é:

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

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

usar uma junção:

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 médio para este conjunto de resultados:

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

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

É óbvio que a segunda tentativa produz resultados enganosos no nosso caso de uso, dado que conta duplicados para o cálculo do valor médio. Também é evidente esse uso de distinct com a declaração de junção irá Não eliminar o problema, dado que irá erroneamente manter uma das três ocorrências da pontuação 3.9. O caso correto é contabilizar para duas (2) ocorrências da pontuação 3.9 dado que nós realmente temos dois (2) estudantes com essa pontuação que cumprem os nossos critérios de consulta.

A documentação do MSDN para o servidor SQL diz

Muitas declarações Transact - SQL que incluem subqueries podem ser formuladas alternativamente como junções. Outras questões só podem ser colocadas com subquimas. Em Transact-SQL, geralmente não há diferença de desempenho entre uma afirmação que inclui um subquery e uma versão semanticamente equivalente que não. No entanto, em alguns casos em que a existência deve ser verificada, uma junção produz um melhor desempenho. Caso contrário, o aninhado a consulta deve ser processada para cada resultado da consulta externa para garantir a eliminação de duplicados. Em tais casos, uma abordagem conjunta produziria melhores resultados.

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

Tente usar o join em vez disso. Noutros casos, não faz diferença.

Eu digo: Criar funções para subqueries eliminar o problema da desordem e permite que você implemente lógica adicional para subqueries. Então eu recomendo a criação de funções para subqueries possivel.

SELECT * 
FROM crv.workorder_details wd 
inner join  crv.workorder wr on wr.workorder_id = wd.workorder_id;

Resultado: 13, 3 Segundos

select * 
from crv.workorder_details 
where workorder_id in (select workorder_id from crv.workorder)

Resultado: 1, 65 Segundos

Corra numa base de dados muito grande a partir de um antigo 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;

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

0 segundos

Novamente, os mesmos resultados, o mesmo número de linhas examinadas. O meu palpite é que é distinto. mos_content.catid leva muito mais tempo para descobrir do que distinto mos_categories.id faz.

As sub-séries são geralmente usadas para devolver uma única linha como um valor Atómico, embora possam ser usadas para comparar valores com múltiplas linhas com a palavra-chave IN. Eles são permitidos em quase qualquer ponto significativo em uma declaração SQL, incluindo a lista de alvos, a cláusula onde, e assim por diante. Uma simples sub-consulta pode ser usada como condição de pesquisa. Por exemplo, entre um par de tabelas:

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

Note que a utilização de um operador de valor normal nos resultados de uma sub-consulta requer que apenas um o campo tem de ser devolvido. Se estiver interessado em verificar a existência de um único valor dentro de um conjunto de outros valores, utilize em:

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

Isto é obviamente diferente de dizer uma saída à esquerda onde você só quer juntar as coisas da mesa A E B, Mesmo que a condição de adesão não encontre nenhum registro correspondente na tabela B, etc.

MySQL version: 5.5.28-0ubuntu0. 12. 04. 2-log

Eu também tinha a impressão de que juntar é sempre melhor do que uma sub-consulta em MySQL, mas explicar é uma maneira melhor de fazer um julgamento. Aqui está um exemplo onde sub consultas funcionam melhor do que se junta.

Aqui está a minha consulta com 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;

Explicar mostra:

+----+--------------------+----------+--------+-----------------------------------------------------+--------------+---------+-------------------------------------------------+------+--------------------------+
| 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 |
+----+--------------------+----------+--------+-----------------------------------------------------+--------------+---------+-------------------------------------------------+------+--------------------------+

A mesma consulta com JOINs é:

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;

E a saída é:

+----+-------------+-------+--------+-----------------------------------------------------+--------------+---------+---------------------------------------------+------+----------------------------------------------+
| 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                     |
+----+-------------+-------+--------+-----------------------------------------------------+--------------+---------+---------------------------------------------+------+----------------------------------------------+

Uma comparação da coluna de rows diz a diferença e a consulta com JOINs está usando Using temporary; Using filesort.

É claro que quando faço ambas as consultas, a primeira é feita em 0,02 segundos, a segunda Não completa mesmo depois de 1 minuto, então explique estas consultas corretamente.

Se Eu não tiver a junção interna na Tabela list_tag, ou seja, se eu remover

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

Da primeira consulta e de forma correspondente:

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

A partir da segunda consulta, em seguida, explicar retorna o mesmo número de linhas para ambas as consultas e ambas estas consultas são igualmente rápidas.

• uma regra geral é que As juntas são mais rápidas na maioria dos casos (99%).
• Quanto mais tabelas de dados têm, as subqueries são mais lentas.
• Quanto menos tabelas de dados têm, as subqueries têm velocidade equivalente à das ligações .
• as subcontas são mais simples, mais fáceis de compreender e mais fáceis de ler.
• A maior parte dos frameworks web e app e os seus "ORM"s e "Active record"s geram consultas com subqueries , porque com as subcontas {[4] } são mais fáceis de dividir a responsabilidade, manter o código, etc.
• para os sítios web mais pequenos ou para as sub-consultas , as sub-consultas estão bem, mas para os sítios web e aplicações maiores, terá frequentemente de reescrever as consultas geradas para juntar as consultas, especialmente se uma consulta usar muitas sub-consultas na consulta.

Algumas pessoas dizem que "alguns RDBMS pode reescrever um subconsulta para join ou join para subconsulta quando ele pensa que é mais rápido do que restante.", mas esta afirmação se aplica a casos simples, certamente não para consultas complicadas com subqueries que realmente causam problemas no desempenho.

As Subqueries têm a capacidade de calcular funções de agregação numa altura. Por exemplo, encontrar o preço mínimo do livro e obter todos os livros que são vendidos com este preço. 1) Utilizar Subqueries:

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

2) utilizar junções

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;

A diferença só é observada quando a segunda tabela de junção tem significativamente mais dados do que a tabela primária. Tive uma experiência como a de baixo...

Tínhamos uma tabela de utilizadores de cem mil entradas e os seus dados de membros (amizade) de cerca de trezentos mil entradas. Foi uma declaração conjunta para levar amigos e seus dados, mas com um grande atraso. Mas estava a funcionar bem, onde havia apenas uma pequena quantidade de dados na tabela de membros. Uma vez que mudamos para use uma sub-consulta que funcionou bem.

Mas, entretanto, as consultas de adesão estão a trabalhar com outras tabelas que têm menos entradas do que a tabela primária.

Primeiro estou a usar o LEFT JOIN porque acho que é a maneira correcta, o mysql pode optimizar, etc. Segunda vez apenas para testar, eu reescrevo para sub-selecionar contra a junção.

Tempo de execução da junção à esquerda: 1, 12 s SUB-SELECCIONAR tempo de execução: 0, 06 s

Depende de vários factores, incluindo a consulta específica que está a executar, a quantidade de dados na sua base de dados. Subquery executa as consultas internas primeiro e, em seguida, a partir do conjunto de resultados novamente filtrar os resultados reais. Enquanto em join executa o e produz o resultado em uma tentativa.

A melhor estratégia é testar tanto a solução de junção como a solução de subcontingente para obter a solução optimizada.

Se quiser acelerar a sua pesquisa usando o join:

Para"junção/junção interior", Não use onde condição em vez de usá-lo em "ON" condição. Eg:

     select id,name from table1 a  
   join table2 b on a.name=b.name
   where id='123'

 Try,

    select id,name from table1 a  
   join table2 b on a.name=b.name and a.id='123'

Para "Junção Esquerda / Direita", Não use em" ON " condição, porque se você usar a junção esquerda / direita ele vai obter todas as linhas para qualquer um table.So não adianta usá-lo em "On". Então, tente usar a condição "onde"