Junção SQL: em que cláusula contra cláusula on
Depois de o ler, este é não um duplicado de explícitas vs implícitas ligações SQL. A resposta pode estar relacionada (ou mesmo a mesma), mas a Pergunta é diferente.
Qual é a diferença e o que deve ir em cada um?
Se eu entender a teoria correctamente, o Optimizador da consulta deve ser capaz de usar ambos indistintamente.
14 answers
Considere estas questões:
SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
WHERE Orders.ID = 12345
E
SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
AND Orders.ID = 12345
O primeiro devolve uma ordem e as suas linhas, se existirem, para o número de ordem 12345
. A segunda irá devolver todas as ordens, mas apenas a ordem 12345
terá quaisquer linhas associadas a ela.
Com um INNER JOIN
, as cláusulas são efectivamente equivalentes. No entanto, só porque são funcionalmente os mesmos, na medida em que produzem os mesmos resultados, não significa que os dois tipos de as cláusulas têm o mesmo significado semântico.
- não importa para as juntas interiores
-
Questões para as juntas exteriores
A.
WHERE
cláusula: Após a adesão. Os registos serão filtrados após a junção ter ocorrido.B.
ON
cláusula - antes da adesão . Os registos (da tabela da direita) serão filtrados antes da adesão. Isto pode acabar como nulo no resultado (desde a junção externa).
Exemplo : considere o seguinte quadros:
1. documents:
| id | name |
--------|-------------|
| 1 | Document1 |
| 2 | Document2 |
| 3 | Document3 |
| 4 | Document4 |
| 5 | Document5 |
2. downloads:
| id | document_id | username |
|------|---------------|----------|
| 1 | 1 | sandeep |
| 2 | 1 | simi |
| 3 | 2 | sandeep |
| 4 | 2 | reya |
| 5 | 3 | simi |
A) no interior WHERE
Cláusula:
SELECT documents.name, downloads.id
FROM documents
LEFT OUTER JOIN downloads
ON documents.id = downloads.document_id
WHERE username = 'sandeep'
For above query the intermediate join table will look like this.
| id(from documents) | name | id (from downloads) | document_id | username |
|--------------------|--------------|---------------------|-------------|----------|
| 1 | Document1 | 1 | 1 | sandeep |
| 1 | Document1 | 2 | 1 | simi |
| 2 | Document2 | 3 | 2 | sandeep |
| 2 | Document2 | 4 | 2 | reya |
| 3 | Document3 | 5 | 3 | simi |
| 4 | Document4 | NULL | NULL | NULL |
| 5 | Document5 | NULL | NULL | NULL |
After applying the `WHERE` clause and selecting the listed attributes, the result will be:
| name | id |
|--------------|----|
| Document1 | 1 |
| Document2 | 3 |
B) no interior JOIN
Cláusula
SELECT documents.name, downloads.id
FROM documents
LEFT OUTER JOIN downloads
ON documents.id = downloads.document_id
AND username = 'sandeep'
For above query the intermediate join table will look like this.
| id(from documents) | name | id (from downloads) | document_id | username |
|--------------------|--------------|---------------------|-------------|----------|
| 1 | Document1 | 1 | 1 | sandeep |
| 2 | Document2 | 3 | 2 | sandeep |
| 3 | Document3 | NULL | NULL | NULL |
| 4 | Document4 | NULL | NULL | NULL |
| 5 | Document5 | NULL | NULL | NULL |
Notice how the rows in `documents` that did not match both the conditions are populated with `NULL` values.
After Selecting the listed attributes, the result will be:
| name | id |
|------------|------|
| Document1 | 1 |
| Document2 | 3 |
| Document3 | NULL |
| Document4 | NULL |
| Document5 | NULL |
Em {[[0]} s eles são intercambiáveis, e o Optimizador irá reorganizá-los à vontade.
Em OUTER JOIN
s, não são necessariamente intercambiáveis, dependendo de que lado da junção dependem.
A maneira que eu faço é:
Coloque sempre as condições de adesão na cláusula sobre Se você estiver fazendo uma junção interior, então não adicione quaisquer condições onde à cláusula on, coloque-as na cláusula where
Se estiver a fazer uma junção à esquerda, adicione todas as condições de onde à cláusula em para a tabela do lado direito da junção. Esta é uma obrigação porque adicionando uma cláusula onde que referencia o lado direito da junção irá converter a junção para uma junção interior (com uma exceção descrita abaixo).
A excepção é que quando você está procurando os registros que não estão em uma tabela em particular, você adicionaria a referência a um identificador único(que nunca é nulo) na tabela de junção direita para a cláusula onde desta forma "Onde t2.idfield é nulo". Então a única vez que você deve referenciar uma tabela no lado direito da junção é encontrar os registros que não estão na tabela.
A cláusula onde filtra tanto o lado esquerdo como o direito da junção, enquanto a cláusula ON filtra sempre apenas o lado direito.
- se quiser sempre obter as linhas do lado esquerdo e só se juntar se alguma condição corresponder, então deverá usar a cláusula em.
- Se quiser filtrar o produto da União ambos os lados, então você deve usar a cláusula onde.
Há uma grande diferença entre em que cláusula vs. cláusula, quando se trata de juntar à esquerda.
Aqui está o exemplo:
mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| fid | int(11) | NO | | NULL | |
| v | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
Lá está o id do quadro t2.
mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| v | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
Consulta sobre a cláusula "on"" :
mysql> SELECT * FROM `t1` left join t2 on fid = t2.id AND t1.v = 'K'
-> ;
+----+-----+---+------+------+
| id | fid | v | id | v |
+----+-----+---+------+------+
| 1 | 1 | H | NULL | NULL |
| 2 | 1 | B | NULL | NULL |
| 3 | 2 | H | NULL | NULL |
| 4 | 7 | K | NULL | NULL |
| 5 | 5 | L | NULL | NULL |
+----+-----+---+------+------+
5 rows in set (0.00 sec)
Pesquisa sobre "where clause"":
mysql> SELECT * FROM `t1` left join t2 on fid = t2.id where t1.v = 'K';
+----+-----+---+------+------+
| id | fid | v | id | v |
+----+-----+---+------+------+
| 4 | 7 | K | NULL | NULL |
+----+-----+---+------+------+
1 row in set (0.00 sec)
É claro que, a primeira consulta retorna um registro de t1 e sua linha dependente de t2, se houver, para a linha t1.v = "K".
A segunda pergunta devolve linhas de t1, mas apenas para t1.v = ' K ' terá qualquer linha associada com ele.
Em SQL,a cláusula "onde" e " ON "são um tipo de Statemants condicionais, mas a principal diferença entre eles é, a cláusula "onde" é usada em declarações de seleção/Atualização para especificar as condições, enquanto a cláusula " ON " é usada em Joins, onde verifica ou verifica se os registros são correspondentes nas tabelas alvo e fonte, antes que os quadros sejam unidos
Por exemplo: - " em que:'
Seleccione * do empregado Onde emprego_id=101
Por exemplo: - 'ON'
*Existem dois quadros: employee_details, as colunas correspondentes são employee_id.*
Espero ter respondido à tua pergunta.Retroceder para obter esclarecimentos.Seleccionar * do empregado entrada interna empregoem empregado.employee_id=employe_details.emprego_id
Para melhores tabelas de desempenho deve ter uma coluna indexada especial para usar para junções .
Então, se a coluna que você condiciona não é uma dessas colunas indexadas, então suspeito que é melhor mantê-la onde está .Então você se junta usando as colunas indexadas, então depois de se juntar você executa a condição na coluna nenhuma indexada .
Normalmente, a filtragem é processada na cláusula "WHERE", uma vez que os dois quadros já foram unidos. É possível, embora você possa querer filtrar uma ou ambas as tabelas antes de se juntar a elas. ou seja, a cláusula "onde" aplica-se a todo o resultado fixado, enquanto a cláusula " on " apenas se aplica à junção em questão.
Para uma junção interior, WHERE
e ON
podem ser usados indistintamente. Na verdade, é possível usar {[[2]} num subcontingente correlacionado. Por exemplo:
update mytable
set myscore=100
where exists (
select 1 from table1
inner join table2
on (table2.key = mytable.key)
inner join table3
on (table3.key = table2.key and table3.key = table1.key)
...
)
Isto é (IMHO) completamente confuso para um humano, e é muito fácil esquecer-se de ligar {[[4]} a qualquer coisa (porque a mesa do "condutor" não tem uma cláusula "on"), mas é legal.
SELECT song_ID,songs.fullname, singers.fullname
FROM music JOIN songs ON songs.ID = music.song_ID
JOIN singers ON singers.ID = music.singer_ID
GROUP BY songs.fullname
Você deve ter o GROUP BY
para fazê-lo funcionar.