Auto-associação MySQL

Resumo : neste tutorial, você aprenderá como usar o self join do MySQL , que une uma tabela a si mesma usando a junção interna ou junção esquerda.

Introdução ao MySQL Self Join

Uma auto-junção permite que você junte uma tabela a ela mesma. Como o MySQL não possui uma sintaxe específica de auto-junção, você precisa realizar uma auto-junção por meio de uma junção regular, como left join ou inner join .

Como você faz referência à mesma tabela em uma única consulta, é necessário usar aliases de tabela para atribuir um nome temporário à tabela ao referenciá-la pela segunda vez.

Para realizar uma auto-associação, siga estas etapas:

• Alias ​​de uma tabela: atribua a cada instância da tabela um alias exclusivo para diferenciá-las.
• Especifique a condição de junção: Defina como as linhas de cada instância da tabela devem ser comparadas. Em uma autojunção, você normalmente compara valores em colunas na mesma tabela.
• Selecione as colunas desejadas: especifique as colunas que deseja incluir no conjunto de resultados final.

Na prática, você usa uma autojunção para consultar dados hierárquicos, como exibir a estrutura da organização ou comparar linhas na mesma tabela.

Exemplos de auto-junção do MySQL

Usaremos a employeestabela do banco de dados de exemplo .

A tabela employeesarmazena não apenas os dados dos funcionários, mas também os dados da organização. Ele usa a reportstocoluna para determinar o ID do gerente de um funcionário.

1) Executando uma auto-junção usando uma junção interna

Para obter toda a estrutura da organização, você pode realizar uma auto-junção na employeestabela usando as colunas employeeNumbere :reportsTo

SELECT 
    CONCAT(m.lastName, ', ', m.firstName) AS Manager,
    CONCAT(e.lastName, ', ', e.firstName) AS 'Direct report'
FROM
    employees e
INNER JOIN employees m ON 
    m.employeeNumber = e.reportsTo
ORDER BY 
    Manager;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Experimente

A saída exibe apenas os funcionários que possuem um gerente. Porém, você não vê o Presidente porque seu nome está filtrado devido à INNER JOINcláusula.

2) Executando uma auto-junção usando uma junção à esquerda

O Presidente é o funcionário que não possui nenhum gestor ou valor na reportsTocoluna é NULL.

A declaração a seguir usa a LEFT JOINcláusula em vez de INNER JOINpara incluir o Presidente:

SELECT 
    IFNULL(CONCAT(m.lastname, ', ', m.firstname),
            'Top Manager') AS 'Manager',
    CONCAT(e.lastname, ', ', e.firstname) AS 'Direct report'
FROM
    employees e
LEFT JOIN employees m ON 
    m.employeeNumber = e.reportsto
ORDER BY 
    manager DESC;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Experimente

3) Usando uma autojunção para comparar linhas sucessivas na mesma tabela

Ao usar o self join do MySQL, você pode exibir uma lista de clientes que estão localizados na mesma cidade associando a customerstabela a ela mesma.

SELECT 
    c1.city, 
    c1.customerName, 
    c2.customerName
FROM
    customers c1
INNER JOIN customers c2 ON 
    c1.city = c2.city
    AND c1.customername > c2.customerName
ORDER BY 
    c1.city;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Experimente

Neste exemplo, a tabela customersé unida a si mesma usando as seguintes condições de união:

• c1.city = c2.city  garante que ambos os clientes tenham a mesma cidade.
• c1.customerName > c2.customerNamegarante que o resultado não inclua o mesmo cliente.

Resumo

• O self-join do MySQL é uma técnica que une uma tabela a si mesma.
• Use aliases de tabela e junção interna ou junção esquerda para realizar uma junção automática no MySQL.