Auto-junção SQLite

Resumo : neste tutorial, você aprenderá sobre um tipo especial de junção chamada auto-junção SQLite, que permite unir uma tabela a ela mesma.

Observe que você deve estar familiarizado com  as cláusulas INNER JOINe LEFT JOINantes de prosseguir com este tutorial.

Introdução à auto-junção SQLite

A auto-junção é um tipo especial de junção que permite unir uma tabela a ela mesma usando a cláusula LEFT JOINou INNER JOIN. Você usa a autojunção para criar um conjunto de resultados que une as linhas com outras linhas na mesma tabela.

Como não é possível fazer referência à mesma tabela mais de uma vez em uma consulta, você precisa usar um alias de tabela para atribuir à tabela um nome diferente ao usar a autojunção.

A autojunção compara valores de colunas iguais ou diferentes na mesma tabela. Apenas uma tabela está envolvida na autojunção.

Você costuma usar a autojunção para consultar o relacionamento pai/filho armazenado em uma tabela ou para obter totais acumulados.

Exemplos de auto-junção SQLite

Usaremos a employeestabela do banco de dados de exemplo para demonstração.

A employeestabela armazena não apenas dados de funcionários, mas também dados organizacionais. A ReportsTocoluna especifica a relação de subordinação entre os funcionários.

Se um funcionário se reporta a um gerente, o valor da ReportsTocoluna da linha do funcionário é igual ao valor da EmployeeIdcoluna da linha do gerente. Caso um funcionário não se reporte a ninguém, a ReportsTocoluna é NULL.

Para obter informações sobre quem é o subordinado direto de quem, você usa a seguinte declaração:

SELECT m.firstname || ' ' || m.lastname AS 'Manager',
       e.firstname || ' ' || e.lastname AS 'Direct report' 
FROM employees e
INNER JOIN employees m ON m.employeeid = e.reportsto
ORDER BY manager;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Tente

A instrução usou a INNER JOINcláusula para unir o employeesa si mesmo. A employeesmesa tem duas funções: funcionários e gerentes.

Como usamos a INNER JOINcláusula para unir a employeestabela a si mesma, o conjunto de resultados não possui a linha cuja coluna do gerenciador contém um NULLvalor.

Observe que o operador de concatenação || concatena várias strings em uma única string. No exemplo, usamos o operador de concatenação para extrair os nomes completos dos funcionários concatenando nome, espaço e sobrenome.

Caso queira consultar o CEO que não se reporta a ninguém, é necessário alterar cláusula INNER JOINpor LEFT JOINcláusula da consulta acima.

Andrew Adamsé o CEO porque ele não denuncia ninguém.

Você pode usar a técnica de auto-associação para localizar os funcionários localizados na mesma cidade da seguinte consulta:

SELECT DISTINCT
	e1.city,
	e1.firstName || ' ' || e1.lastname AS fullname
FROM
	employees e1
INNER JOIN employees e2 ON e2.city = e1.city 
   AND (e1.firstname <> e2.firstname AND e1.lastname <> e2.lastname)
ORDER BY
	e1.city;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Tente

A condição de junção tem duas expressões:

• e1.city = e2.citypara garantir que ambos os funcionários localizados na mesma cidade
• e.firstname <> e2.firstname AND e1.lastname <> e2.lastnamepara garantir que e1e e2não sejam o mesmo funcionário, presumindo que não existam funcionários com o mesmo nome e sobrenome.

Neste tutorial, mostramos como usar a técnica de auto-junção SQLite para unir uma tabela a ela mesma.