SQL JOIN e diferentes tipos de JOINs

O que é um SQL JOIN e quais são os diferentes tipos?

Uma ilustração da W3schools :


INNER JOIN - Apenas registros que correspondem à condição em ambas as tabelas


LEFT JOIN - Todos os registros da tabela 1 em conjunto com os registros que correspondem à condição na tabela 2


JUNTA DIREITA - Todos os registros da tabela 2 em conjunto com os registros da tabela 1 que correspondem à condição


FULL OUTER JOIN - Combinação de Left e Right Outer une-se à cláusula ON, mas preserva ambas as tabelas


O que é SQL JOIN ?

SQL JOIN é um método para recuperar dados de duas ou mais tabelas de database.

Quais são os diferentes SQL JOIN s?

Há um total de cinco JOIN s. Eles são :

  1. JOIN or INNER JOIN 2. OUTER JOIN 2.1 LEFT OUTER JOIN or LEFT JOIN 2.2 RIGHT OUTER JOIN or RIGHT JOIN 2.3 FULL OUTER JOIN or FULL JOIN 3. NATURAL JOIN 4. CROSS JOIN 5. SELF JOIN 

1. JOIN ou INNER JOIN:

Nesse tipo de JOIN , obtemos todos os registros que correspondem à condição nas duas tabelas e os registros nas duas tabelas que não correspondem não são relatados.

Em outras palavras, INNER JOIN é baseado no fato de que: SOMENTE as inputs correspondentes em ambas as tabelas devem ser listadas.

Observe que um JOIN sem qualquer outra JOIN chave do JOIN (como INNER , OUTER , LEFT , etc) é um INNER JOIN . Em outras palavras, JOIN é um açúcar sintático para INNER JOIN (veja: Diferença entre JOIN e INNER JOIN ).

2. OUTER JOIN:

OUTER JOIN recupera

As linhas correspondentes de uma tabela e todas as linhas da outra tabela Ou todas as linhas de todas as tabelas (não importa se há ou não correspondência).

Existem três tipos de associação externa:

2,1 LEFT OUTER JOIN ou LEFT JOIN

Essa união retorna todas as linhas da tabela à esquerda em conjunto com as linhas correspondentes da tabela à direita. Se não houver colunas correspondentes na tabela à direita, ele retornará valores NULL .

2.2 DIREITO OUTER JOIN ou RIGHT JOIN

Este JOIN retorna todas as linhas da tabela da direita em conjunto com as linhas correspondentes da tabela da esquerda. Se não houver colunas correspondentes na tabela à esquerda, ele retornará valores NULL .

2.3 FULL OUTER JOIN ou FULL JOIN

Este JOIN combina LEFT OUTER JOIN e RIGHT OUTER JOIN . Ele retorna linhas de qualquer tabela quando as condições são atendidas e retorna o valor NULL quando não há correspondência.

Em outras palavras, OUTER JOIN é baseado no fato de que: SOMENTE as inputs correspondentes em uma das tabelas (direita ou esquerda) ou ambas as tabelas (completo) devem ser listadas.

 Note that `OUTER JOIN` is a loosened form of `INNER JOIN`. 

3. JUNÇÃO NATURAL:

É baseado nas duas condições:

  1. o JOIN é feito em todas as colunas com o mesmo nome para igualdade.
  2. Remove colunas duplicadas do resultado.

Isso parece ser mais de natureza teórica e como resultado (provavelmente) a maioria dos SGBD não se incomoda em apoiar isso.

4. CROSS JOIN:

É o produto cartesiano das duas tabelas envolvidas. O resultado de um CROSS JOIN não fará sentido na maioria das situações. Além disso, não precisaremos disso (ou precisaremos do mínimo, para ser preciso).

5. SELF JOIN:

Não é uma forma diferente de JOIN , mas é um JOIN ( INNER , OUTER , etc) de uma tabela para si mesmo.

JOINs baseados em operadores

Dependendo do operador usado para uma cláusula JOIN , pode haver dois tipos de JOIN s. Eles são

  1. Equi JOIN
  2. Theta JOIN

1. Equi JOIN:

Para qualquer tipo de JOIN ( INNER , OUTER , etc), se usarmos APENAS o operador de igualdade (=), então dizemos que o JOIN é um EQUI JOIN .

2. Theta JOIN:

Isso é o mesmo que EQUI JOIN mas permite que todos os outros operadores gostem de>, <,> = etc.

Muitos consideram tanto o EQUI JOIN quanto o Theta JOIN semelhantes a INNER , OUTER etc JOIN s. Mas eu acredito fortemente que é um erro e torna as idéias vagas. Porque INNER JOIN , OUTER JOIN etc estão todos conectados com as tabelas e seus dados enquanto EQUI JOIN e THETA JOIN são conectados apenas com os operadores que usamos no primeiro.

Novamente, há muitos que consideram o NATURAL JOIN como uma espécie de “peculiar” EQUI JOIN . De fato, é verdade, por causa da primeira condição que mencionei para o NATURAL JOIN . No entanto, não precisamos restringir isso simplesmente ao NATURAL JOIN s sozinho. INNER JOIN s, OUTER JOIN s etc. também pode ser um EQUI JOIN .

Definição:


JOINS é uma maneira de consultar os dados que são combinados de várias tabelas simultaneamente.

Tipos de JOINS:


Preocupação com RDBMS existem 5 tipos de junções:

  • Equi-Join: combina registros comuns de duas tabelas com base na condição de igualdade. Tecnicamente, Join feito usando-se operador-de-igualdade (=) para comparar valores de PrimaryKey de uma tabela e valores Foriegn Key da tabela antoher, portanto, o conjunto de resultados inclui registros comuns (combinados) de ambas as tabelas. Para implementação, veja INNER-JOIN.

  • Natural-Join: É uma versão aprimorada do Equi-Join, na qual a operação SELECT omite a coluna duplicada. Para implementação, veja INNER-JOIN

  • Non-Equi-Join: É reverso do Equi-join, onde a condição de junit é usada com operador diferente de igual (=), por exemplo,! =, <=,> =,>,

  • Self-Join:: Um comportamento personalizado de join, onde uma tabela é combinada consigo mesma; Isso é normalmente necessário para consultar tabelas de auto-referência (ou entidade de relacionamento Unário). Para implementação, veja INNER-JOINs.

  • Produto cartesiano: Cruz combina todos os registros de ambas as tabelas sem qualquer condição. Tecnicamente, ele retorna o conjunto de resultados de uma consulta sem WHERE-Clause.

Conforme a preocupação e o avanço do SQL, há três tipos de junções e todas as junções do RDBMS podem ser obtidas usando esses tipos de junções.

  1. INNER-JOIN: Ele mescla (ou combina) as linhas correspondentes de duas tabelas. A correspondência é feita com base em colunas comuns de tabelas e em sua operação de comparação. Se condição baseada em igualdade, então: EQUI-JOIN executado, caso contrário, não-EQUI-Join.

  2. ** OUTER-JOIN: ** Mescla (ou combina) linhas correspondentes de duas tabelas e linhas sem correspondência com valores NULL. No entanto, a seleção personalizada de linhas não correspondidas, por exemplo, selecionando linha não correspondida da primeira tabela ou segunda tabela por subtipos: LEFT OUTER JOIN e RIGHT OUTER JOIN.

    2.1. LEFT Outer JOIN (aka, LEFT-JOIN): Retorna linhas correspondidas que formam duas tabelas e são unhasched da tabela LEFT (ou seja, primeira tabela) apenas.

    2.2. RIGHT Outer JOIN (também conhecido como RIGHT-JOIN): retorna linhas correspondidas de duas tabelas e unmatched da tabela RIGHT.

    2.3. FULL OUTER JOIN (também conhecido como OUTER JOIN): retorna correspondido e inigualável de ambas as tabelas.

  3. CROSS-JOIN: Esta junit não mescla / combina, ao invés disso, executa um produto cartisiano.

insira a descrição da imagem aqui Nota: O Self-JOIN pode ser obtido por INNER-JOIN, OUTER-JOIN e CROSS-JOIN com base no requisito, mas a tabela deve se unir a ele.

Para maiores informações:

Exemplos:

1.1: INNER-JOIN: Implementação Equi-join

 SELECT * FROM Table1 A INNER JOIN Table2 B ON A. =B.; 

1.2: INNER-JOIN: Implementação Natural-JOIN

 Select A.*, B.Col1, B.Col2 --But no B.ForiengKyeColumn in Select FROM Table1 A INNER JOIN Table2 B On A.Pk = B.Fk; 

1.3: INNER-JOIN com implementação de não-eqijoin

 Select * FROM Table1 A INNER JOIN Table2 B On A.Pk <= B.Fk; 

1.4: INNER-JOIN com SELF-JOIN

 Select * FROM Table1 A1 INNER JOIN Table1 A2 On A1.Pk = A2.Fk; 

2.1: OUTER JOIN (junit externa completa)

 Select * FROM Table1 A FULL OUTER JOIN Table2 B On A.Pk = B.Fk; 

2.2: LEFT JOIN

 Select * FROM Table1 A LEFT OUTER JOIN Table2 B On A.Pk = B.Fk; 

2.3: JUNTA DIREITA

 Select * FROM Table1 A RIGHT OUTER JOIN Table2 B On A.Pk = B.Fk; 

3.1: CROSS JOIN

 Select * FROM TableA CROSS JOIN TableB; 

3.2: CROSS JOIN-Self JOIN

 Select * FROM Table1 A1 CROSS JOIN Table1 A2; 

//OU//

 Select * FROM Table1 A1,Table1 A2; 

Curiosamente, a maioria das outras respostas sofre desses dois problemas:

  • Eles se concentram em formas básicas de se unir apenas
  • Eles (ab) usam diagramas de Venn, que são uma ferramenta imprecisa para visualizar joins (eles são muito melhores para sindicatos) .

Recentemente eu escrevi um artigo sobre o tópico: Um guia provavelmente incompleto e abrangente para as muitas maneiras diferentes de juntar tabelas em SQL , que vou resumir aqui.

Em primeiro lugar: JOINs são produtos cartesianos

É por isso que os diagramas de Venn os explicam de forma tão imprecisa, porque um JOIN cria um produto cartesiano entre as duas tabelas unidas. Wikipedia ilustra bem:

insira a descrição da imagem aqui

A syntax SQL para produtos cartesianos é CROSS JOIN . Por exemplo:

 SELECT * -- This just generates all the days in January 2017 FROM generate_series( '2017-01-01'::TIMESTAMP, '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day', INTERVAL '1 day' ) AS days(day) -- Here, we're combining all days with all departments CROSS JOIN departments 

Que combina todas as linhas de uma tabela com todas as linhas da outra tabela:

Fonte:

 +--------+ +------------+ | day | | department | +--------+ +------------+ | Jan 01 | | Dept 1 | | Jan 02 | | Dept 2 | | ... | | Dept 3 | | Jan 30 | +------------+ | Jan 31 | +--------+ 

Resultado:

 +--------+------------+ | day | department | +--------+------------+ | Jan 01 | Dept 1 | | Jan 01 | Dept 2 | | Jan 01 | Dept 3 | | Jan 02 | Dept 1 | | Jan 02 | Dept 2 | | Jan 02 | Dept 3 | | ... | ... | | Jan 31 | Dept 1 | | Jan 31 | Dept 2 | | Jan 31 | Dept 3 | +--------+------------+ 

Se apenas escrevermos uma lista de tabelas separadas por vírgulas, obteremos o mesmo:

 -- CROSS JOINing two tables: SELECT * FROM table1, table2 

JUNTA INTERNA (Theta-JOIN)

Um INNER JOIN é apenas um CROSS JOIN filtrado onde o predicado do filtro é chamado Theta na álgebra relacional.

Por exemplo:

 SELECT * -- Same as before FROM generate_series( '2017-01-01'::TIMESTAMP, '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day', INTERVAL '1 day' ) AS days(day) -- Now, exclude all days/departments combinations for -- days before the department was created JOIN departments AS d ON day >= d.created_at 

Observe que a palavra-chave INNER é opcional (exceto no MS Access).

( veja o artigo para exemplos de resultados )

EQUI JOIN

Um tipo especial de Theta-JOIN é o equi JOIN, que usamos mais. O predicado associa a chave primária de uma tabela à chave estrangeira de outra tabela. Se usarmos o database Sakila para ilustração, podemos escrever:

 SELECT * FROM actor AS a JOIN film_actor AS fa ON a.actor_id = fa.actor_id JOIN film AS f ON f.film_id = fa.film_id 

Isso combina todos os atores com seus filmes.

Ou também, em alguns bancos de dados:

 SELECT * FROM actor JOIN film_actor USING (actor_id) JOIN film USING (film_id) 

A syntax USING() permite especificar uma coluna que deve estar presente nos dois lados das tabelas da operação JOIN e cria um predicado de igualdade nessas duas colunas.

JUNÇÃO NATURAL

Outras respostas listaram este “tipo JOIN” separadamente, mas isso não faz sentido. É apenas uma forma de açúcar de syntax para equi JOIN, que é um caso especial de Theta-JOIN ou INNER JOIN. O JOIN NATURAL simplesmente coleta todas as colunas comuns a ambas as tabelas que estão sendo unidas e une USING() essas colunas. O que quase nunca é útil, devido a correspondências acidentais (como LAST_UPDATE colunas LAST_UPDATE no database Sakila ).

Aqui está a syntax:

 SELECT * FROM actor NATURAL JOIN film_actor NATURAL JOIN film 

OUTER JOIN

Agora, OUTER JOIN é um pouco diferente do INNER JOIN , pois cria um UNION de vários produtos cartesianos. Nós podemos escrever:

 -- Convenient syntax: SELECT * FROM a LEFT JOIN b ON  -- Cumbersome, equivalent syntax: SELECT a.*, b.* FROM a JOIN b ON  UNION ALL SELECT a.*, NULL, NULL, ..., NULL FROM a WHERE NOT EXISTS ( SELECT * FROM b WHERE  ) 

Ninguém quer escrever o último, então escrevemos OUTER JOIN (que geralmente é melhor otimizado por bancos de dados).

Assim como o INNER , a palavra-chave OUTER é opcional aqui.

OUTER JOIN vem em três sabores:

  • LEFT [ OUTER ] JOIN : A tabela da esquerda da expressão JOIN é adicionada à união como mostrado acima.
  • RIGHT [ OUTER ] JOIN : A tabela à direita da expressão JOIN é adicionada à união como mostrado acima.
  • FULL [ OUTER ] JOIN : Ambas as tabelas da expressão JOIN são adicionadas à união como mostrado acima.

Tudo isso pode ser combinado com a palavra chave USING() ou com NATURAL ( na verdade eu tive um caso de uso do mundo real para um NATURAL FULL JOIN recentemente )

Sintaxes alternativas

Existem algumas syntaxs históricas e obsoletas no Oracle e no SQL Server, que já suportavam o OUTER JOIN antes que o padrão SQL tivesse uma syntax para isso:

 -- Oracle SELECT * FROM actor a, film_actor fa, film f WHERE a.actor_id = fa.actor_id(+) AND fa.film_id = f.film_id(+) -- SQL Server SELECT * FROM actor a, film_actor fa, film f WHERE a.actor_id *= fa.actor_id AND fa.film_id *= f.film_id 

Tendo dito isso, não use essa syntax. Acabei de listar isso aqui para que você possa reconhecê-lo a partir de posts antigos / código legado.

Particionado OUTER JOIN

Poucas pessoas sabem disso, mas o padrão SQL especifica o OUTER JOIN particionado (e o Oracle o implementa). Você pode escrever coisas assim:

 WITH -- Using CONNECT BY to generate all dates in January days(day) AS ( SELECT DATE '2017-01-01' + LEVEL - 1 FROM dual CONNECT BY LEVEL <= 31 ), -- Our departments departments(department, created_at) AS ( SELECT 'Dept 1', DATE '2017-01-10' FROM dual UNION ALL SELECT 'Dept 2', DATE '2017-01-11' FROM dual UNION ALL SELECT 'Dept 3', DATE '2017-01-12' FROM dual UNION ALL SELECT 'Dept 4', DATE '2017-04-01' FROM dual UNION ALL SELECT 'Dept 5', DATE '2017-04-02' FROM dual ) SELECT * FROM days LEFT JOIN departments PARTITION BY (department) -- This is where the magic happens ON day >= created_at 

Partes do resultado:

 +--------+------------+------------+ | day | department | created_at | +--------+------------+------------+ | Jan 01 | Dept 1 | | -- Didn't match, but still get row | Jan 02 | Dept 1 | | -- Didn't match, but still get row | ... | Dept 1 | | -- Didn't match, but still get row | Jan 09 | Dept 1 | | -- Didn't match, but still get row | Jan 10 | Dept 1 | Jan 10 | -- Matches, so get join result | Jan 11 | Dept 1 | Jan 10 | -- Matches, so get join result | Jan 12 | Dept 1 | Jan 10 | -- Matches, so get join result | ... | Dept 1 | Jan 10 | -- Matches, so get join result | Jan 31 | Dept 1 | Jan 10 | -- Matches, so get join result 

O ponto aqui é que todas as linhas do lado particionado da junit acabarão no resultado, independentemente de o JOIN correspondido a qualquer coisa no “outro lado do JOIN”. Longa história curta: Isso é para preencher dados esparsos nos relatórios. Muito útil!

SEMI JOIN

A sério? Nenhuma outra resposta conseguiu isso? Claro que não, porque não tem uma syntax nativa no SQL, infelizmente (assim como o ANTI JOIN abaixo). Mas podemos usar IN() e EXISTS() , por exemplo, para encontrar todos os atores que já tocaram em filmes:

 SELECT * FROM actor a WHERE EXISTS ( SELECT * FROM film_actor fa WHERE a.actor_id = fa.actor_id ) 

O WHERE a.actor_id = fa.actor_id predicado WHERE a.actor_id = fa.actor_id atua como o predicado semi join. Se você não acredita, verifique os planos de execução, por exemplo, no Oracle. Você verá que o database executa uma operação SEMI JOIN, não o predicado EXISTS() .

insira a descrição da imagem aqui

ANTI JOIN

Isso é exatamente o oposto do SEMI JOIN ( tenha cuidado para não usar o NOT IN , pois ele tem uma advertência importante)

Aqui estão todos os atores sem filmes:

 SELECT * FROM actor a WHERE NOT EXISTS ( SELECT * FROM film_actor fa WHERE a.actor_id = fa.actor_id ) 

Algumas pessoas (especialmente pessoas do MySQL) também escrevem ANTI JOIN assim:

 SELECT * FROM actor a LEFT JOIN film_actor fa USING (actor_id) WHERE film_id IS NULL 

Eu acho que o motivo histórico é o desempenho.

JUNTA LATERAL

OMG, esse aqui é muito legal. Eu sou o único a mencionar isso? Aqui está uma consulta legal:

 SELECT a.first_name, a.last_name, f.* FROM actor AS a LEFT OUTER JOIN LATERAL ( SELECT f.title, SUM(amount) AS revenue FROM film AS f JOIN film_actor AS fa USING (film_id) JOIN inventory AS i USING (film_id) JOIN rental AS r USING (inventory_id) JOIN payment AS p USING (rental_id) WHERE fa.actor_id = a.actor_id -- JOIN predicate with the outer query! GROUP BY f.film_id ORDER BY revenue DESC LIMIT 5 ) AS f ON true 

Ele vai encontrar a receita TOP 5 produzindo filmes por ator. Toda vez que você precisar de uma consulta TOP-N-por-algo, LATERAL JOIN será seu amigo. Se você é uma pessoa do SQL Server, então você sabe esse tipo de JOIN sob o nome APPLY

 SELECT a.first_name, a.last_name, f.* FROM actor AS a OUTER APPLY ( SELECT f.title, SUM(amount) AS revenue FROM film AS f JOIN film_actor AS fa ON f.film_id = fa.film_id JOIN inventory AS i ON f.film_id = i.film_id JOIN rental AS r ON i.inventory_id = r.inventory_id JOIN payment AS p ON r.rental_id = p.rental_id WHERE fa.actor_id = a.actor_id -- JOIN predicate with the outer query! GROUP BY f.film_id ORDER BY revenue DESC LIMIT 5 ) AS f 

OK, talvez isso seja trapaça, porque uma expressão LATERAL JOIN ou APPLY é realmente uma “subconsulta correlacionada” que produz várias linhas. Mas se permitirmos “subconsultas correlacionadas”, também podemos falar sobre …

MULTISET

Isso só é realmente implementado pelo Oracle e pelo Informix (que eu saiba), mas pode ser emulado no PostgreSQL usando arrays e / ou XML e no SQL Server usando XML.

MULTISET produz uma subconsulta correlacionada e aninha o conjunto resultante de linhas na consulta externa. A consulta abaixo seleciona todos os atores e, para cada ator, coleta seus filmes em uma coleção aninhada:

 SELECT a.*, MULTISET ( SELECT f.* FROM film AS f JOIN film_actor AS fa USING (film_id) WHERE a.actor_id = fa.actor_id ) AS films FROM actor 

Como você viu, existem mais tipos de JOIN do que apenas o “chato” INNER , OUTER e CROSS JOIN que são normalmente mencionados. Mais detalhes no meu artigo . E, por favor, pare de usar os diagramas de Venn para ilustrá-los.

No SQL Server, existem diferentes tipos de JOINS.

  1. CROSS JOIN
  2. JUNÇÃO INTERNA
  3. OUTER JOIN

As junções externas são novamente divididas em 3 tipos

  1. Junção Esquerda ou Junção Externa Esquerda
  2. Junção Direita ou Junção Externa Direita
  3. Junção completa ou junit externa completa

insira a descrição da imagem aqui

insira a descrição da imagem aqui

JOIN ou INNER JOIN

 SELECT Name, Gender, Salary, DepartmentName FROM tblEmployee INNER JOIN tblDepartment ON tblEmployee.DepartmentId = tblDepartment.Id OR SELECT Name, Gender, Salary, DepartmentName FROM tblEmployee JOIN tblDepartment ON tblEmployee.DepartmentId = tblDepartment.Id 

insira a descrição da imagem aqui

LEFT JOIN ou LEFT OUTER JOIN

 SELECT Name, Gender, Salary, DepartmentName FROM tblEmployee LEFT OUTER JOIN tblDepartment ON tblEmployee.DepartmentId = tblDepartment.Id OR SELECT Name, Gender, Salary, DepartmentName FROM tblEmployee LEFT JOIN tblDepartment ON tblEmployee.DepartmentId = tblDepartment.Id 

insira a descrição da imagem aqui

JUNTA DIREITA ou DIREITA EXTERIOR

 SELECT Name, Gender, Salary, DepartmentName FROM tblEmployee RIGHT OUTER JOIN tblDepartment ON tblEmployee.DepartmentId = tblDepartment.Id OR SELECT Name, Gender, Salary, DepartmentName FROM tblEmployee RIGHT JOIN tblDepartment ON tblEmployee.DepartmentId = tblDepartment.Id 

insira a descrição da imagem aqui

JUNTA COMPLETA OU JUNTA COMPLETA

 SELECT Name, Gender, Salary, DepartmentName FROM tblEmployee FULL OUTER JOIN tblDepartment ON tblEmployee.DepartmentId = tblDepartment.Id OR SELECT Name, Gender, Salary, DepartmentName FROM tblEmployee FULL JOIN tblDepartment ON tblEmployee.DepartmentId = tblDepartment.Id 

insira a descrição da imagem aqui

insira a descrição da imagem aqui

insira a descrição da imagem aqui

Eu criei uma ilustração que explica melhor do que palavras, na minha opinião: SQL Junta tabela de explicação

Vou empurrar a minha implicância: a palavra-chave USING.

Se ambas as tabelas em ambos os lados do JOIN tiverem suas foreign keys corretamente nomeadas (ou seja, mesmo nome, não apenas “id”), então isso pode ser usado:

 SELECT ... FROM customers JOIN orders USING (customer_id) 

Acho isso muito prático, legível e não é usado com freqüência suficiente.