mysql 子连接_MySQL 子连接示例

Summary:in this tutorial, you will learn how to useMySQL self jointhat joins a table to itself using join statement.

In the previous tutorial, you have learned how to join a table to the other tables usingINNER JOIN,LEFT JOINorRIGHT JOINstatement. However, there is a special case that you can join a table to itself, which is known as self join.

You use self join when you want to combine records with other records in the same table. To perform the self join operation, you must use atable aliasto help MySQLdistinguish the left table from the right table of the same table.

MySQL Self Join Examples

Let’s take a look at theemployeestable in the sample database.

In theemployeestable, we store not only employees data but also organization structure data. Thereportstocolumn is used to determine the manager ID of an employee.

In order to get the whole organization structure, we can join theemployeestable to itself using theemployeeNumberandreportsTocolumns. Theemployeestable has two roles: one isManagerand the other isDirect Report.

SELECTCONCAT(m.lastname,',',m.firstname)AS'Manager',CONCAT(e.lastname,',',e.firstname)AS'Directreport'FROMemployeeseINNERJOINemployeesmONm.employeeNumber=e.reportstoORDERBYmanager

In the above example, we only see employees who have manager. However, we don’t see the top manager because his name is filtered out due to theINNER JOINclause. The top manager is the employee who does not have manager or his manager no. isNULL.

Let’s change theINNER JOINclause to theLEFT JOINclause in the query above to include the top manager. We also need to use theIFNULLfunction to display the top manager if the manger’s name isNULL.

SELECTIFNULL(CONCAT(m.lastname,',',m.firstname),'TopManager')AS'Manager',CONCAT(e.lastname,',',e.firstname)AS'Directreport'FROMemployeeseLEFTJOINemployeesmONm.employeeNumber=e.reportstoORDERBYmanagerDESC

By using MySQL self join, we can display a list of customers who locate in the same city byjoiningthecustomerstable to itself.

SELECTc1.city,c1.customerName,c2.customerNameFROMcustomersc1INNERJOINcustomersc2ONc1.city=c2.cityANDc1.customername<>c2.customerNameORDERBYc1.city

We joined thecustomerstable to itself with the following join conditions:c1.city = c2.cityto make sure that both customers have the same city

c.customerName <> c2.customerNameto ensure that we don’t get the same customer.

In this tutorial, we have introduced you to MySQLselfjoin that allows you to join a table to itself by usingINNER JOINorLEFT JOINclauses.

Related TutorialsMySQL INNER JOIN

MySQL LEFT JOIN

原文链接：http://outofmemory.cn/mysql/mysql-self-join