Menu

Tutorial do MySQL JOIN: interno, externo, cruzado, esquerdo, direito e próprio

  • Principal
  • Outro
  • Tutorial do MySQL JOIN: interno, externo, cruzado, esquerdo, direito e próprio

mysql join tutorial inner

Experimente Nosso Instrumento Para Eliminar Problemas

Aprenda sobre as diferentes declarações JOIN do MySQL como Inner, Outer, Cross, Left, Right e Self com sintaxe e exemplos de programação:

Neste tutorial, aprenderemos sobre o MySQL JOIN e também entenderemos os diferentes tipos de Joins que são suportados no MySQL. JOIN em sua forma mais simples pode ser considerado um meio de recuperar / atualizar ou excluir dados de várias tabelas em uma única consulta.

Então, basicamente, JOIN combina 2 ou mais tabelas para buscar dados em uma determinada condição.

O MySQL também suporta outras maneiras diferentes de consultar dados de múltiplas tabelas usando - subconsultas e combinando múltiplas consultas usando UNION, etc.

MySQL Joins

O que você aprenderá:

Tabelas normalizadas

Em bancos de dados MySQL normalizados, as tabelas têm relacionamentos com colunas comuns por meio de restrições de chave como chaves estrangeiras.

Vamos tentar entender isso com a ajuda de um exemplo - Suponha que haja 2 tabelas, EMPLOYEE e EMPLOYEE_DEPARTMENT. Agora, em um banco de dados desnormalizado - ou seja, uma única tabela com todas as colunas, muitas informações, por exemplo, sobre o Departamento, seriam duplicadas, pois pode haver muitos funcionários que fazem parte do mesmo Departamento.

Portanto, para reduzir essas duplicações e economizar no armazenamento, os bancos de dados são mantidos em um estado normalizado.

Portanto, neste caso, nos referiríamos ao Departamento por um campo Department_id na tabela EMPLOYEE e todas as informações relevantes relacionadas ao Departamento - como Informações do Departamento, Chefe do Departamento, etc. podem ser mantidas como parte da tabela EMPLOYEE_DEPARTMENT.

Portanto, em poucas palavras - EMPLOYEE e EMPLOYEE_DEPARTMENT estão vinculados um ao outro por meio do campo Department_id, que está atuando como FOREIGN KEY para a tabela EMPLOYEE e PRIMARY KEY para a tabela EMPLOYEE_DEPARTMENT.

A imagem abaixo é uma representação pictórica tendo uma relação entre essas duas tabelas por meio de Restrição de chave estrangeira

Restrição de chave estrangeira

MySQL JOIN

MySQL JOIN é usado para buscar, atualizar ou deletar dados de 2 ou mais tabelas em uma determinada condição. Portanto, JOIN é sempre usado em conjunto com instruções SELECT, UPDATE ou DELETE

Sintaxe do comando JOIN:

PARTICIPE com SELECT

SELECT {column_list} FROM tableName1 {joinType} tableName2 ON {joinCondition}

PARTICIPE com UPDATE

DELETE FROM tableName1 {joinType} tableName2 ON {joinCondition} WHERE {condition}

PARTICIPE com DELETE

UPDATE tableName1 SET targetColumn = targetValue FROM tableName1 {joinType} tableName2 ON {joinCondition} WHERE {condition}

Observe que JOIN pode ser aplicado a várias tabelas em uma única consulta, mas para simplificar, vamos primeiro tentar entender o uso de Joins com 2 tabelas.

As diferentes partes da sintaxe incluem:

  • {column_list} - Isso representa os nomes das colunas que queremos recuperar como resultado de nossa consulta.
  • {JoinType} - Isso indica o tipo de associação que estamos aplicando. Existem diferentes tipos de JOINS que podem buscar dados:
    • JUNÇÃO INTERNA
    • OUTER JOIN
      • LEFT OUTER JOIN
      • DIREITO OUTER JOIN
    • CROSS JOIN

Aprenderemos sobre todos esses diferentes tipos de JOINS do MySQL nas próximas seções do tutorial.

  • {JoinCondition} - Estas são as condições da coluna que seriam usadas para o JOIN para consultar e buscar dados.

Na próxima seção, discutiremos os diferentes tipos de Joins que estão disponíveis no MySQL.

Tipos de MySQL JOIN

Preparação de dados de amostra

Para utilizar o JOINS, utilizaremos 2 tabelas Employee e Employee_Department com os detalhes abaixo. Você pode usar / referir-se a um site útil aqui para gerar dados fictícios para o esquema.

Listando consultas para criação e inserção de tabela:

CREATE TABLE IF NOT EXISTS Employee_Department ( name varchar(100), id INT NOT NULL auto_increment, PRIMARY KEY (id) ); CREATE TABLE IF NOT EXISTS Employee ( name varchar(100), id int not null auto_increment, address varchar(100), Department_id int, PRIMARY KEY (id), FOREIGN KEY (Department_id) references Employee_Department(id) ); INSERT INTO `Employee_Department` VALUES ('Information Technology','1'), ('HR','2'), ('Finance','3'), ('Accounting','4'), ('Housekeeping','5'), ('Security','6'), ('Support','7'), ('Contract Staff','8'), ('Sales','9'), ('Management','10'); INSERT INTO `Employee` VALUES ('Veniam','1','640 Damon Junction East Mathew, NY 68818','3'), ('Molestiae','2','6658 Hollis Club Ernamouth, TX 19743','10'), ('Officiis','3','59965 Mason Neck Apt. 985 Kareemborough, NV 85535','9'), ('Rerum','4','91067 Geovany Fort Hanefort, WA 92863','6'), ('Et','5','7647 Reva Shores Suite 970 New Audrafort, OH 17846-5397','2'), ('Et','6','9419 Carmela Burg Apt. 687 Aimeebury, SD 32389-4489','8'), ('Laborum','7','6961 Weissnat Drive Donnellyfort, MT 53947','6'), ('Cupiditate','8','117 Nellie Summit Suite 982 South Heavenfurt, CA 45675','8'), ('Eveniet','9','9086 Mariam Square Suite 698 South Dulce, MT 82861-3079','2'), ('Rerum','10','783 Goodwin Burgs Apt. 429 Willmsfort, UT 42820-1019','9'), ('Quis','11','42928 Ernesto Trail East Jules, WV 87169-2851','1'), ('Esse','12','161 Kassulke Stravenue Apt. 937 Williamsonton, MS 62622','7'), ('Dolores','13','22811 Liliana Trail Apt. 890 South Ernesto, MT 04134','10'), ('Ut','14','981 Laron Overpass Suite 361 West Olahaven, FL 46982-7801','10'), ('Mollitia','15','411 Louisa Mill South Maximefort, MA 04903','8'), ('Necessitatibus','16','2853 Jonathon Turnpike Quitzonville, KY 54776','4'), ('Fugit','17','3647 Rosalinda Corner Maureenstad, RI 96605','5'), ('Corporis','18','03559 Nicholas Circle Apt. 364 West Zettaberg, ID 58592-3447','8'), ('Neque','19','56111 Alysson Gateway Apt. 212 Leschbury, VT 90605-2306','8'), ('Velit','20','263 O'Keefe Avenue Apt. 884 West Missouri, IL 50303','6'),

JUNÇÃO INTERNA

INNER JOIN é a forma mais comum de JOIN e é amplamente utilizada. Quase todos vocês deveriam ter usado isso em algum momento ou outro. Vamos ver a sintaxe e, a seguir, alguns exemplos de consultas.

Sintaxe:

SELECT {column_list} FROM tableName1 INNER JOIN tableName2 ON {joinCondition}

Consulta para buscar nomes_de_departamentos para todos os funcionários das tabelas declaradas acima (Employee e Employee_Department):

SELECT Employee.name as Employee_name, Employee_Department.name as Department_name FROM Employee INNER JOIN Employee_Department ON Employee.Department_id = Employee_Department.id

Aqui está o resultado da consulta acima:

Nome do empregadoNome do departamento
as coisasSegurança
QuemTecnologia da Informação
ERH
cairRH
euFinança
precisaContabilidade
FgitServiço de limpeza
lanchesSegurança
por favorSegurança
EsseApoio, suporte
EContratada
ânsiaContratada
resiliênciaContratada
corpoContratada
JustoContratada
escritóriosVendas
as coisasVendas
aborrecimentosGestão
DoresGestão
ForaGestão

Observação: Aqui, usamos apelidos de nome de coluna. Exemplo: Employee.name como Employee_name - apenas para tornar os resultados mais legíveis e abrangentes.

Vamos modificar esta consulta para buscar apenas os nomes que começam com a letra ‘m’.

SELECT Employee.name as Employee_name, Employee_Department.name as Department_name FROM Employee INNER JOIN Employee_Department ON Employee.Department_id = Employee_Department.id WHERE Employee.name like 'm%'

Abaixo está a saída:

Nome do empregadoNome do departamento
lanches76961 Weissnat Drive
Donnellyfort, MT 53947		6NULONULONULO
aborrecimentosGestão
resiliênciaContratada

E agora vamos entender o INNER JOIN com a ajuda do Diagrama de Venn conforme abaixo. As linhas resultantes retornadas consistem nos dados sobrepostos entre as duas tabelas, conforme correspondido à condição JOIN.

mySQL-innerJoin

OUTER JOIN

OUTER JOIN é usado para buscar dados de 2 ou mais tabelas com a exceção de incluir linhas não correspondentes (ou linhas com dados nulos para as colunas consultadas).

Para entender o Outer Join melhor, adicione uma nova tabela office_locations com campos - id e endereço e adicione uma nova coluna chamada `office_id` à tabela Employee originalmente criada.

Aqui estão as consultas para o mesmo:

CREATE TABLE IF NOT EXISTS office_locations ( address varchar(100), id INT NOT NULL auto_increment, PRIMARY KEY (id) ); ALTER TABLE Employee ADD COLUMN office_id int; INSERT INTO `office_locations`(address) VALUES('Bangalore'), ('Mumbai'), ('Seattle'), ('Santa Clara'); UPDATE Employee SET office_id=1 where id % 2 = 0; UPDATE Employee SET office_id=2 where id % 3 = 0;

Consulte o Diagrama de Venn abaixo para entender OUTER JOINS pictoricamente:

mySQL-outerJoin

Existem 2 tipos de OUTER JOINs

a) JUNÇÃO EXTERNA À ESQUERDA

Como o nome sugere, esse tipo de Join buscaria todas as linhas (incluindo valores NULL) da tabela que está no lado esquerdo da consulta JOIN. Em palavras simples, todos os resultados / linhas que não correspondem à condição JOIN serão retornados com o resultado com valores NULL para a tabela certa.

Por exemplo, precisamos de dados de localização para todos os funcionários - ou seja, descobrir qual é o endereço do escritório de todos os funcionários.

SELECT * from Employee LEFT OUTER JOIN office_locations ON Employee.office_id = office_locations.id

Resultado da consulta acima:

NomeEu iriaEndereçoDepartment_idOffice_idEndereço do escritórioEu iria
eu1640 Damon Junction
East Mathew, NY 68818		3NULONULONULO
aborrecimentosdois6658 Hollis Club
Ernamouth, TX 19743		101Bangalore1
escritórios359965 Mason Neck Apt. 985
Kareemborough, NV 85535		9doisMumbaidois
as coisas491067 Forte Geovany)
Hanefort, WA 92863		61Bangalore1
E57647 Reva Shores Suite 970
Novo Audrafort, OH 17846-5397		doisNULONULONULO
E69419 Carmela Burg Apt. 687
Aimeebury, SD 32389-4489		8doisMumbaidois
ânsia8117 Nellie Summit Suite 982
South Heavenfurt, CA 45675		81Bangalore1
cair99086 Mariam Square Suite 698
South Dulce, MT 82861-3079		doisdoisMumbaidois
as coisas10783 Goodwin Burgs Apt. 429
Willmsfort, UT 42820-1019		91Bangalore1
Quemonze42928 Ernesto Trail
East Jules, WV 87169-2851		1NULONULONULO
Esse12161 Kassulke Stravenue Apt. 937
Williamsonton, MS 62622		7doisMumbaidois
Dores1322811 Liliana Trail Apt. 890
Ernesto do Sul, MT 04134		10NULONULONULO
Fora14981 Suíte Viaduto Laron 361
West Olahaven, FL 46982-7801		101Bangalore1
resiliênciaquinze411 Louisa Mill
South Maximefort, MA 04903		8doisMumbaidois
precisa162853 Jonathon Turnpike
Quitzonville, KY 54776		41Bangalore1
FLED173647 Rosalinda Corner
Maureenstad, RI 96605		5NULONULONULO
corpo1803559 Nicholas Circle Apt. 364
West Zettaberg, ID 58592-3447		8doisMumbaidois
ou1956111 Alysson Gateway Apt. 212
Leschbury, VT 90605-2306		8NULONULONULO
por favorvinte263 O'Keefe Avenue Apt. 884
West Missouri, IL 50303		61Bangalore1

b) JUNÇÃO EXTERNA DIREITA

Semelhante a LEFT JOIN, neste tipo de Join todos os registros que não correspondem à tabela da direita são retornados com valores NULL nas colunas da tabela do lado esquerdo.

melhor ferramenta de gerenciamento de caso de teste para jira

Por exemplo, com nossas tabelas de amostra, se executarmos o RIGHT JOIN na mesma consulta que usamos para LEFT JOIN, obteremos valores NULL para as cidades 'Seattle' e 'Santa Clara', pois não há linhas na tabela Employee com localização definida a esses valores.

SELECT * from Employee RIGHT OUTER JOIN office_locations ON Employee.office_id = office_locations.id

Resultado da consulta acima:

o que é arquivo apk no android
NomeEu iriaEndereçoDepartment_idOffice_idEndereço do escritórioEu iria
por favorvinte263 O'Keefe Avenue Apt. 884
West Missouri, IL 50303		61Bangalore1
aborrecimentosdois6658 Hollis Club
Ernamouth, TX 19743		101Bangalore1
as coisas491067 Geovany Fort
Hanefort, WA 92863		61Bangalore1
ânsia8117 Nellie Summit Suite 982
South Heavenfurt, CA 45675		81Bangalore1
as coisas10783 Goodwin Burgs Apt. 429
Willmsfort, UT 42820-1019		91Bangalore1
Fora14981 Suíte Viaduto Laron 361
West Olahaven, FL 46982-7801		101Bangalore1
precisa162853 Jonathon Turnpike
Quitzonville, KY 54776		41Bangalore1
escritórios359965 Mason Neck Apt. 985
Kareemborough, NV 85535		9doisMumbaidois
E69419 Carmela Burg Apt. 687
Aimeebury, SD 32389-4489		8doisMumbaidois
cair99086 Mariam Square Suite 698
South Dulce, MT 82861-3079		doisdoisMumbaidois
Esse12161 Kassulke Stravenue Apt. 937
Williamsonton, MS 62622		7doisMumbaidois
resiliênciaquinze411 Louisa Mill
South Maximefort, MA 04903		8doisMumbaidois
corpo1803559 Nicholas Circle Apt. 364
West Zettaberg, ID 58592-3447		8doisMumbaidois
NULONULONULONULONULOSeattle3
NULONULONULONULONULOSanta Clara4

Notas / dicas:

  • Em outros bancos de dados relacionais como o Microsoft SQL - você pode encontrar outro tipo de OUTER JOIN, que é denominado FULL OUTER JOIN. Não é nada além de uma combinação de junções INNER e OUTER - ou seja, ele retornará valores NULL das tabelas LEFT e RIGHT.
  • Para uma discussão detalhada junto com a explicação e exemplos sobre as diferenças entre INNER e OUTER JOINS, consulte nosso tutorial aqui.
  • Nas consultas OUTER JOIN - RIGHT OUTER JOIN e LEFT OUTER JOIN podem ser especificados apenas como RIGHT JOIN e LEFT JOIN respectivamente para maior legibilidade.

CROSS JOIN

O CROSS JOIN também é chamado de Produto Cartesiano. Ele retorna um resultado contra as condições de junção correspondentes com um total de m x n linhas, onde m e n são um número de linhas correspondentes na tabela1 e tabela2 contra a condição JOIN.

Vejamos um exemplo de consulta para CROSS JOIN para obter resultados de 2 tabelas - Employee e office_locations

SELECT * from Employee CROSS JOIN office_locations

A saída retornada conterá um total de 80 linhas, que nada mais é do que o produto das linhas em ambas as tabelas - Funcionário (20) x escritórios_locações (4) => 80

Observe que, ao fazer CROSS JOIN, você não precisa especificar nenhuma condição JOIN, pois de qualquer maneira obteria o resultado m x n.

Notas / dicas:

Você não encontrará muitas vezes usando CROSS JOIN, pois não tem muitos casos de uso associados. Esta associação é geralmente executada quando alguém está procurando obter todas as combinações possíveis com os dados de 2 tabelas.

Por exemplo: Suponha que você seja um exportador de roupas e tenha 2 tabelas - uma contendo dados de cores e outra contendo dados de tamanho. Aqui, para garantir o estoque, você pode considerar fazer um CROSS JOIN de ambas as tabelas para garantir que todas as roupas sejam adquiridas para todos os tamanhos e combinações de cores.

SELF JOIN

SELF JOIN é como qualquer outra junção INNER ou OUTER, com as únicas exceções de que ambas as tabelas, ou seja, a tabela de junção e junção, são iguais.

Geralmente usamos SELF Joins quando queremos descobrir a relação entre as linhas da mesma tabela. Por exemplo, se uma tabela tiver id de funcionário e id de funcionário do supervisor, podemos juntar a tabela contra si mesma se quisermos descobrir o nome do gerente em relação ao nome do funcionário.

Observe que para SELF JOIN você deve usar apelidos de tabela para que as condições de junção possam ser especificadas referindo-se à tabela correta.

Vamos ver um exemplo aqui:

SELECT e1.name as Employee1, e2.name as Employee2 from Employee e1 inner join Employee e2 on e1.office_id = e2.office_id where e1.Department_id = e2.Department_id

Aqui, juntamos a tabela Funcionário a ela mesma para descobrir os nomes de Funcionários que têm a mesma localização de escritório e Departamento

Funcionário 1Funcionário 2
Eresiliência
aborrecimentosaborrecimentos
aborrecimentosFora
Oficiisescritórios
as coisasas coisas
as coisaspor favor
EE
Ecorpo
ânsiaânsia
caircair
as coisasas coisas
EsseEsse
Foraaborrecimentos
ForaFora
resiliênciaE
resiliênciaresiliência
resiliênciacorpo
precisaprecisa
corpoE
corporesiliência
corpocorpo
por favoras coisas
por favorpor favor

Como discutimos, SELF JOIN é apenas ter tabelas Joining e Join da mesma forma, podemos usar SELF JOIN com INNER ou OUTER Joins.

MySQL JOIN with UPDATE and DELETE

Até agora, discutimos associações com instruções SELECT. No entanto, Joins também podem ser usados ​​com instruções MySQL DELETE e UPDATE.

A sintaxe permanece a mesma aqui. Vejamos alguns exemplos para entender melhor o conceito.

ATUALIZAR com INNER JOIN

Suponha que desejemos alterar o endereço na tabela Employee para o nome do local do escritório que está presente na tabela office_locations. Aqui, podemos usar INNER JOIN para buscar o nome da cidade em office_locations e atualizar a mesma consulta.

Consulta de amostra:

UPDATE Employee emp inner join office_locations off_loc on emp.office_id = off_loc.id set emp.address = off_loc.address;

Semelhante a isso, UPDATE também pode ser usado com outros tipos de Joins, dependendo do requisito em relação a casos de uso específicos.

DELETE com INNER JOIN

Usaremos as tabelas Employee e Employee_Departments como exemplo. Suponha que desejemos excluir todos os registros de Funcionários que pertencem ao Departamento de vendas e também a entrada para o Departamento de vendas.

Já que usamos Department_id como a restrição FOREIGN KEY, devemos primeiro DROP essa restrição antes de usar DELETE com JOIN para várias tabelas.

Vamos primeiro descobrir o nome da restrição que foi criada para Department_id na tabela Employee. Basta executar o comando para obter o comando CREATE TABLE.

show create table Employee

Você obterá a saída como:

CREATE TABLE `Employee` ( `name` varchar(100) DEFAULT NULL, `id` int NOT NULL AUTO_INCREMENT, `address` varchar(100) DEFAULT NULL, `Department_id` int DEFAULT NULL, `office_id` int DEFAULT NULL, PRIMARY KEY (`id`), KEY `Department_id` (`Department_id`), CONSTRAINT `Employee_ibfk_1` FOREIGN KEY (`Department_id`) REFERENCES `Employee_Department` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Agora você pode se referir ao nome da restrição FOREIGN_KEY do comando acima, que é ‘Employee_ibfk_1’ e podemos usar esse nome para eliminar essa restrição da tabela Employee executando o comando abaixo:

alter table Employee drop constraint Employee_ibfk_1

Agora vamos executar o comando DELETE com INNER JOIN

delete emp_dept, emp from Employee emp inner join Employee_Department emp_dept on emp.Department_id = emp_dept.id where emp.Department_id = 9

A saída desse comando seria não. de linhas excluídas (e isso deve EXCLUIR com sucesso as linhas de ambas as tabelas referenciadas na consulta JOIN acima)

perguntas frequentes

P # 1) Quantos tipos de Joins existem no MySQL?

Responda: O MySQL oferece suporte principalmente a 3 tipos de Joins. Esses são:

  • Junção interna
  • Junção externa - junção externa esquerda e junção externa direita
  • Cross Join

P # 2) O Join pode ser usado apenas para 2 mesas?

Responda: As associações podem ser aplicadas a 2 ou mais de 2 tabelas. Na maioria das vezes, Joins são usados ​​para 2 tabelas.

Abaixo está um exemplo de INNER JOIN usando 3 tabelas:

select Employee.name as emp_name, Employee_Department.name as dept_name, office_locations.address as office_location from Employee inner join Employee_Department on Employee.Department_id = Employee_Department.id inner join office_locations on Employee.office_id = office_locations.id

Resultado da consulta acima:

emp_namedept_namelocalização do escritório
ForaGestãoBangalore
aborrecimentosGestãoBangalore
as coisasSegurançaBangalore
EContratadaMumbai
ânsiaContratadaBangalore
cairRHMumbai
EsseApoio, suporteMumbai
resiliênciaContratadaMumbai
precisaContabilidadeBangalore
corpoContratadaMumbai
por favorSegurançaBangalore

P # 3) Como usar aliases de tabela com Joins?

Responda: Aliases são uma forma de ter um nome temporário para uma tabela a ser referenciada em uma consulta. Como as consultas JOIN às vezes são complexas e para torná-las legíveis e evitar referência a nomes de tabelas para cada referência de coluna, podemos ter nomes abreviados com aliases.

Vamos escrever uma consulta INNER JOIN para buscar dados para todos os nomes de funcionários e nomes de departamentos das tabelas Employee e Employee_Departments, respectivamente.

select emp.name as emp_name, Department.name as dept_name from Employee emp inner join Employee_Department as Department on emp.Department_id = Department.id

Na consulta acima, você pode ver que temos a tabela Employee com alias como emp e a tabela Employee_Department como Departamento e usamos os aliases para fazer referência a nomes de colunas e condições de associação.

P # 4) O que é CROSS JOIN?

Responda: CROSS JOIN é um tipo de Join onde o usuário deseja buscar o produto cartesiano das tabelas que estão sendo associadas.

O resultado de CROSS JOIN é m x n, em que m é o número de linhas correspondentes na primeira tabela e n é o número de linhas correspondentes na segunda tabela.

Q # 5) Como obter FULL OUTER JOIN no MySQL

Responda: O MySQL não fornece FULL OUTER JOIN como um tipo de junção separado, ao contrário de outros bancos de dados como o Microsoft SQL. No entanto, para obter resultados como um FULL OUTER JOIN teria obtido, podemos combinar os resultados de LEFT OUTER JOIN e RIGHT OUTER JOIN.
Por exemplo, podemos buscar detalhes de Funcionário e Departamento e aplicar união a Associações Externas Esquerda e Direita.

Leitura recomendada = >> Operador MySQL Union

Conclusão

Neste tutorial, aprendemos sobre os diferentes tipos de Joins disponíveis no MySQL.

Discutimos INNER, OUTER, CROSS e SELF Joins e também vimos como as consultas JOIN podem ser usadas com instruções UPDATE e DELETE para ter consultas eficientes e otimizadas para dados combinados entre 2 ou mais de 2 tabelas.

JOINS são uma das consultas mais básicas usadas e qualquer pessoa que use ou aprenda bancos de dados baseados em SQL deve ter um conhecimento completo de SQL Joins.

Leitura recomendada

^