Sql 简明教程

SQL - Self Join

Self Join ，顾名思义，是一种将表的记录与其自身组合的联接类型。

Self Join, as its name suggests, is a type of join that combines the records of a table with itself.

假设一个组织在组织圣诞派对时，根据某些颜色在员工中选择一位秘密圣诞老人。它的设计是为每位员工分配一种颜色，并让他们从各种颜色池中选择一种颜色。最终，他们将成为分配给此颜色的员工的秘密圣诞老人。

Suppose an organization, while organizing a Christmas party, is choosing a Secret Santa among its employees based on some colors. It is designed to be done by assigning one color to each of its employees and having them pick a color from the pool of various colors. In the end, they will become the Secret Santa of an employee this color is assigned to.

正如我们从下图中可以看到的，有关所分配颜色的信息和每位员工选择的颜色的信息被输入到一个表中。该表使用颜色列上的自联接将自己与其他表联接，以将员工与其秘密圣诞老人相匹配。

As we can see in the figure below, the information regarding the colors assigned and a color each employee picked is entered into a table. The table is joined to itself using self join over the color columns to match employees with their Secret Santa.

The SQL Self Join

SQL Self Join 用于将一张表联接到自己，就好像该表是两张表一样。要执行此操作，应至少使用一次表别名。

The SQL Self Join is used to join a table to itself as if the table were two tables. To carry this out, alias of the tables should be used at least once.

自联接是一种内部联接类型，在需要比较同一张表的两列的情况下执行；可能要建立它们之间的关系。换句话说，当一张表同时包含 Foreign Key 和 Primary Key 时，它会与自己联接。

Self Join is a type of inner join, which is performed in cases where the comparison between two columns of a same table is required; probably to establish a relationship between them. In other words, a table is joined with itself when it contains both Foreign Key and Primary Key in it.

Syntax

以下是 SQL 自联接的基本语法−

Following is the basic syntax of SQL Self Join −

SELECT column_name(s)
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;

这里，WHERE 子句可以是基于您要求的任何给定表达式。

Here, the WHERE clause could be any given expression based on your requirement.

Example

自联接只需要一张表，所以，让我们创建一个 CUSTOMERS 表，其中包含客户详细信息，例如他们的姓名、年龄、地址和收入。

Self Join only requires one table, so, let us create a CUSTOMERS table containing the customer details like their names, age, address and the salary they earn.

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

现在，使用如下所示的 INSERT 语句向此表中插入值：

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

该表将被创建为：

The table will be created as −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

现在，让我们使用以下自联接查询联接此表。我们的目标是根据收入建立上述客户之间的关系。我们借助 WHERE 子句来执行此操作。

Now, let us join this table using the following Self Join query. Our aim is to establish a relationship among the said Customers on the basis of their earnings. We are doing this with the help of the WHERE clause.

SELECT a.ID, b.NAME as EARNS_HIGHER, a.NAME
as EARNS_LESS, a.SALARY as LOWER_SALARY
FROM CUSTOMERS a, CUSTOMERS b
WHERE a.SALARY < b.SALARY;

Output

显示的结果表将列出所有收入低于其他客户的客户−

The resultant table displayed will list out all the customers that earn lesser than other customers −

ID	EARNS_HIGHER	EARNS_LESS	LOWER_SALARY
2	Ramesh	Khilan	1500.00
2	Kaushik	Khilan	1500.00
6	Chaitali	Komal	4500.00
3	Chaitali	Kaushik	2000.00
2	Chaitali	Khilan	1500.00
1	Chaitali	Ramesh	2000.00
6	Hardik	Komal	4500.00
4	Hardik	Chaitali	6500.00
3	Hardik	Kaushik	2000.00
2	Hardik	Khilan	1500.00
1	Hardik	Ramesh	2000.00
3	Komal	Kaushik	2000.00
2	Komal	Khilan	1500.00
1	Komal	Ramesh	2000.00
6	Muffy	Komal	4500.00
5	Muffy	Hardik	8500.00
4	Muffy	Chaitali	6500.00
3	Muffy	Kaushik	2000.00
2	Muffy	Khilan	1500.00
1	Muffy	Ramesh	2000.00

Self Join with ORDER BY Clause

在使用自联接将一张表与其自身联接后，也可以使用 ORDER BY 子句按顺序对合并表中的记录进行排序。

After joining a table with itself using self join, the records in the combined table can also be sorted in an order, using the ORDER BY clause.

Syntax

以下为此提供的语法−

Following is the syntax for it −

SELECT column_name(s)
FROM table1 a, table1 b
WHERE a.common_field = b.common_field
ORDER BY column_name;

Example

让我们使用 WHERE 子句对自己连接 CUSTOMERS 表；然后，使用 ORDER BY 子句按照指定的列对记录进行升序排列，如下图所示查询所示。

Let us join the CUSTOMERS table with itself using self join on a WHERE clause; then, arrange the records in an ascending order using the ORDER BY clause with respect to a specified column, as shown in the following query.

SELECT  a.ID, b.NAME as EARNS_HIGHER, a.NAME
as EARNS_LESS, a.SALARY as LOWER_SALARY
FROM CUSTOMERS a, CUSTOMERS b
WHERE a.SALARY < b.SALARY
ORDER BY a.SALARY;

Output

最终表如下所示：

The resultant table is displayed as follows −

ID	EARNS_HIGHER	EARNS_LESS	LOWER_SALARY
2	Ramesh	Khilan	1500.00
2	Kaushik	Khilan	1500.00
2	Chaitali	Khilan	1500.00
2	Hardik	Khilan	1500.00
2	Komal	Khilan	1500.00
2	Muffy	Khilan	1500.00
3	Chaitali	Kaushik	2000.00
1	Chaitali	Ramesh	2000.00
3	Hardik	Kaushik	2000.00
1	Hardik	Ramesh	2000.00
3	Komal	Kaushik	2000.00
1	Komal	Ramesh	2000.00
3	Muffy	Kaushik	2000.00
1	Muffy	Ramesh	2000.00
6	Chaitali	Komal	4500.00
6	Hardik	Komal	4500.00
6	Muffy	Komal	4500.00
4	Hardik	Chaitali	6500.00
4	Muffy	Chaitali	6500.00
5	Muffy	Hardik	8500.00

不仅仅是薪水列，还可以根据姓名的字母顺序、客户 ID 的数字顺序等对记录进行排序。

Not just the salary column, the records can be sorted based on the alphabetical order of names, numerical order of Customer IDs etc.