SQLite Join Tables: Inner, Natural, Left Outer, Cross (exemplos)

Índice:

Anonim

O SQLite suporta diferentes tipos de SQL Joins, como INNER JOIN, LEFT OUTER JOIN e CROSS JOIN. Cada tipo de JOIN é usado para uma situação diferente, como veremos neste tutorial.

Neste tutorial, você aprenderá-

  • Introdução à cláusula SQLite JOIN
  • JUNÇÃO INTERNA
  • PARTICIPE ... USANDO
  • NATURAL JOIN
  • LEFT OUTER JOIN
  • CROSS JOIN

Introdução à cláusula SQLite JOIN

Quando você está trabalhando em um banco de dados com várias tabelas, geralmente precisa obter dados dessas várias tabelas.

Com a cláusula JOIN, você pode vincular duas ou mais tabelas ou subconsultas unindo-as. Além disso, você pode definir por qual coluna você precisa vincular as tabelas e por quais condições.

Qualquer cláusula JOIN deve ter a seguinte sintaxe:

Cada cláusula de junção contém:

  • Uma tabela ou subconsulta que é a tabela da esquerda; a tabela ou a subconsulta antes da cláusula de junção (à esquerda dela).
  • Operador JOIN - especifique o tipo de junção (INNER JOIN, LEFT OUTER JOIN ou CROSS JOIN).
  • Restrição JOIN - depois de especificar as tabelas ou subconsultas a serem unidas, você precisa especificar uma restrição de união, que será uma condição na qual as linhas correspondentes que correspondem a essa condição serão selecionadas dependendo do tipo de união.

Observe que, para todos os exemplos a seguir, você deve executar o sqlite3.exe e abrir uma conexão com o banco de dados de amostra como fluxo:

Etapa 1) Nesta etapa,

  1. Abra Meu Computador e navegue até o seguinte diretório " C: \ sqlite " e
  2. Em seguida, abra " sqlite3.exe ":

Etapa 2) Abra o banco de dados " TutorialsSampleDB.db " pelo seguinte comando:

Agora você está pronto para executar qualquer tipo de consulta no banco de dados.

SQLite INNER JOIN

O INNER JOIN retorna apenas as linhas que correspondem à condição de junção e elimina todas as outras linhas que não correspondem à condição de junção.

Exemplo

No exemplo a seguir, uniremos as duas tabelas " Students " e " Departments " com DepartmentId para obter o nome do departamento de cada aluno, da seguinte maneira:

SELECIONEAlunos.Nome do Aluno,Departments.DepartmentNameDE AlunosINNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Explicação do código:

O INNER JOIN funciona da seguinte maneira:

  • Na cláusula Select, você pode selecionar quaisquer colunas que deseja selecionar das duas tabelas referenciadas.
  • A cláusula INNER JOIN é escrita após a primeira tabela referenciada com a cláusula "From".
  • Então, a condição de junção é especificada com ON.
  • Aliases podem ser especificados para tabelas referenciadas.
  • A palavra INNER é opcional, você pode simplesmente escrever JOIN.

Resultado:

  • O INNER JOIN produz os registros de ambos - os alunos e as tabelas do departamento que correspondem à condição que é " S tudents.DepartmentId = Departments.DepartmentId ". As linhas sem correspondência serão ignoradas e não incluídas no resultado.
  • É por isso que apenas 8 alunos de 10 alunos retornaram desta consulta com os departamentos de TI, matemática e física. Já os alunos "Jena" e "George" não foram incluídos porque têm um ID de departamento nulo, que não corresponde à coluna departmentId da tabela de departamentos. Como segue:

SQLite JOIN ... USING

O INNER JOIN pode ser escrito usando a cláusula "USING" para evitar redundância, portanto, em vez de escrever "ON Students.DepartmentId = Departments.DepartmentId", você pode simplesmente escrever "USING (DepartmentID)".

Você pode usar "JOIN ... USING" sempre que as colunas que você vai comparar na condição de junção tiverem o mesmo nome. Nesses casos, não há necessidade de repeti-los usando a condição on e apenas informar os nomes das colunas e o SQLite detectará isso.

A diferença entre INNER JOIN e JOIN ... USANDO:

Com "JOIN

… USANDO "você não escreve uma condição de junção, você apenas escreve a coluna de junção que é comum entre as duas tabelas unidas, ao invés de escrever tabela1" INNER JOIN tabela2 ON tabela1.cola = tabela2.cola "nós escrevemos como" table1 JOIN table2 USING (cola) ".

Exemplo

No exemplo a seguir, uniremos as duas tabelas " Students " e " Departments " com DepartmentId para obter o nome do departamento de cada aluno, da seguinte maneira:

SELECIONEAlunos.Nome do Aluno,Departments.DepartmentNameDE AlunosINNER JOIN Departments USING (DepartmentId);

Explicação

  • Ao contrário do exemplo anterior, não escrevemos " ON Students.DepartmentId = Departments.DepartmentId ". Acabamos de escrever " USING (DepartmentId) ".
  • SQLite infere a condição de junção automaticamente e compara o DepartmentId de ambas as tabelas - Alunos e Departamentos.
  • Você pode usar essa sintaxe sempre que as duas colunas que está comparando tiverem o mesmo nome.

Resultado

  • Isso lhe dará o mesmo resultado exato do exemplo anterior:

SQLite NATURAL JOIN

Um NATURAL JOIN é semelhante a um JOIN ... USING, a diferença é que ele testa automaticamente a igualdade entre os valores de cada coluna que existe nas duas tabelas.

A diferença entre INNER JOIN e um NATURAL JOIN:

  • Em INNER JOIN, você deve especificar uma condição de junção que a junção interna usa para juntar as duas tabelas. Enquanto na junção natural, você não escreve uma condição de junção. Você apenas escreve os nomes das duas tabelas sem nenhuma condição. Em seguida, a junção natural testará automaticamente a igualdade entre os valores de cada coluna existente em ambas as tabelas. A junção natural infere a condição de junção automaticamente.
  • No NATURAL JOIN, todas as colunas de ambas as tabelas com o mesmo nome serão comparadas entre si. Por exemplo, se tivermos duas tabelas com dois nomes de coluna em comum (as duas colunas existem com o mesmo nome nas duas tabelas), então a junção natural unirá as duas tabelas comparando os valores de ambas as colunas e não apenas de uma coluna.

Exemplo

SELECIONEAlunos.Nome do Aluno,Departments.DepartmentNameDE AlunosDepartamentos JOIN naturais;

Explicação

  • Não precisamos escrever uma condição de junção com nomes de coluna (como fizemos em INNER JOIN). Nem mesmo precisamos escrever o nome da coluna uma vez (como fizemos em JOIN USING).
  • A junção natural fará a varredura de ambas as colunas das duas tabelas. Ele detectará que a condição deve ser composta pela comparação de DepartmentId de ambas as tabelas Alunos e Departamentos.

Resultado

  • O Natural JOIN fornecerá exatamente a mesma saída que a saída que obtivemos dos exemplos INNER JOIN e JOIN USING. Porque em nosso exemplo todas as três consultas são equivalentes. Mas, em alguns casos, a saída será diferente da junção interna e da junção natural. Por exemplo, se houver mais tabelas com os mesmos nomes, a junção natural combinará todas as colunas umas com as outras. No entanto, a junção interna corresponderá apenas às colunas na condição de junção (mais detalhes na próxima seção; a diferença entre a junção interna e a junção natural).

SQLite LEFT OUTER JOIN

O padrão SQL define três tipos de OUTER JOINs: LEFT, RIGHT e FULL, mas o SQLite suporta apenas LEFT OUTER JOIN.

Em LEFT OUTER JOIN, todos os valores das colunas que você selecionar na tabela à esquerda serão incluídos no resultado da consulta, portanto, independentemente do valor corresponder ou não à condição de junção, ele será incluído no resultado.

Portanto, se a tabela da esquerda tiver 'n' linhas, os resultados da consulta terão 'n' linhas. No entanto, para os valores das colunas provenientes da tabela certa, se houver algum valor que não corresponda à condição de junção, ele conterá um valor "nulo".

Portanto, você obterá um número de linhas equivalente ao número de linhas na junção à esquerda. Assim, você obterá as linhas correspondentes de ambas as tabelas (como os resultados de INNER JOIN), mais as linhas não correspondentes da tabela à esquerda.

Exemplo

No exemplo a seguir, tentaremos o "LEFT JOIN" para unir as duas tabelas "Alunos" e "Departamentos":

SELECIONEAlunos.Nome do Aluno,Departments.DepartmentNameDE Alunos - esta é a mesa da esquerdaLEFT JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Explicação

  • A sintaxe de LEFT JOIN é igual a INNER JOIN; você escreve LEFT JOIN entre as duas tabelas e, em seguida, a condição de junção vem após a cláusula ON.
  • A primeira tabela após a cláusula from é a tabela à esquerda. Enquanto a segunda tabela especificada após a junção à esquerda é a tabela à direita.
  • A cláusula OUTER é opcional; LEFT OUTER JOIN é igual a LEFT JOIN.

Resultado

  • Como você pode ver, todas as linhas da tabela de alunos estão incluídas, sendo 10 alunos no total. Mesmo se o quarto e último aluno, Jena e George, departamentoIds, não existir na tabela Departamentos, eles também serão incluídos.
  • E, nesses casos, o valor departmentName para Jena e George será "nulo" porque a tabela department não tem um departmentName que corresponda ao valor departmentId.

Vamos dar à consulta anterior usando a junção à esquerda uma explicação mais profunda usando diagramas de Van:

O LEFT JOIN fornecerá nomes de todos os alunos da tabela de alunos, mesmo se o aluno tiver um ID de departamento que não existe na tabela de departamentos. Portanto, a consulta não fornecerá apenas as linhas correspondentes como INNER JOIN, mas fornecerá a parte extra que contém as linhas incompatíveis da tabela da esquerda, que é a tabela dos alunos.

Observe que qualquer nome de aluno que não tenha departamento correspondente terá um valor "nulo" para o nome do departamento, porque não há valor correspondente para ele e esses valores são os valores nas linhas não correspondentes.

SQLite CROSS JOIN

Um CROSS JOIN fornece o produto cartesiano para as colunas selecionadas das duas tabelas unidas, combinando todos os valores da primeira tabela com todos os valores da segunda tabela.

Portanto, para cada valor na primeira tabela, você obterá 'n' correspondências da segunda tabela, onde n é o número de linhas da segunda tabela.

Ao contrário de INNER JOIN e LEFT OUTER JOIN, com CROSS JOIN, você não precisa especificar uma condição de junção, porque SQLite não precisa dela para CROSS JOIN.

O SQLite resultará em resultados lógicos definidos combinando todos os valores da primeira tabela com todos os valores da segunda tabela.

Por exemplo, se você selecionou uma coluna da primeira tabela (colA) e outra coluna da segunda tabela (colB). O colA contém dois valores (1,2) e o colB também contém dois valores (3,4).

Então, o resultado do CROSS JOIN será quatro linhas:

  • Duas linhas combinando o primeiro valor de colA que é 1 com os dois valores de colB (3,4) que serão (1,3), (1,4).
  • Da mesma forma, duas linhas combinando o segundo valor de colA que é 2 com os dois valores de colB (3,4) que são (2,3), (2,4).

Exemplo

Na consulta a seguir, tentaremos o CROSS JOIN entre as tabelas de Alunos e Departamentos:

SELECIONEAlunos.Nome do Aluno,Departments.DepartmentNameDE AlunosDepartamentos CROSS JOIN;

Explicação

  • Na cláusula select, apenas selecionamos duas colunas "studentname" da tabela de alunos e "departmentName" da tabela de departamentos.
  • Para a junção cruzada, não especificamos nenhuma condição de junção, apenas as duas tabelas combinadas com CROSS JOIN no meio delas.

Resultado:

Como você pode ver, o resultado são 40 linhas; 10 valores da tabela de alunos comparados com os 4 departamentos da tabela de departamentos. Como segue:

  • Quatro valores para os quatro departamentos da tabela de departamentos combinados com o primeiro aluno Michel.
  • Quatro valores para os Quatro departamentos da tabela de departamentos combinados com o segundo aluno John.
  • Quatro valores para os Quatro departamentos da tabela de departamentos combinados com o terceiro aluno Jack.

    … e assim por diante.

Resumo

Usando SQLite JOINs, você pode vincular uma ou mais tabelas ou subconsultas para selecionar colunas de ambas as tabelas ou subconsultas.