SQL 自连接

自连接 是一种将表的记录与自身结合的连接类型，正如其名称所示。

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

如下图所示，与颜色分配和每个员工选择的颜色相关的信息被输入到一张表中。使用自连接将表格与自身连接起来，通过颜色列将员工与其“秘密圣诞老人”匹配。

SQL 自连接

SQL自连接

SQL自连接用于将一个表连接到自身，就好像该表是两个表一样。为了进行此操作，至少应该使用表的别名一次。

自连接是一种内连接的类型，当需要比较同一表的两列时执行，可能是为了建立它们之间的关系。换句话说，当表中同时包含外键和主键时，它与自身连接。

与其他连接查询不同，我们使用WHERE子句来指定表与自身组合的条件，而不是使用ON子句。

语法

以下是SQL自连接的基本语法：

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

在这里，WHERE从句可以根据您的需求是任何给定的表达式。

示例

自连接仅需要一张表；所以，让我们创建一个CUSTOMERS表，里面包含客户的详细信息，如他们的姓名、年龄、地址和工资。

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语句将值插入该表中，如下所示 –

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, 'MP', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

表将会被创建成−

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	MP	4500.00
7	Muffy	24	Indore	10000.00

现在让我们使用以下自连接查询来加入这个表。我们的目标是根据他们的收入建立客户之间的关系。我们正在使用WHERE子句来实现这一点。

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;

输出：

显示的结果表将列出所有收入较低的客户：

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

自连接和ORDER BY子句

在自连接时，可以使用ORDER BY子句对合并表中的记录进行排序。

语法

以下是其语法：

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

示例

让我们继续使用下面展示的相同的CUSTOMERS表 –

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	MP	4500.00
7	Muffy	24	Indore	10000.00

在WHERE子句上使用自连接，让我们将CUSTOMERS表与自身连接；然后，使用ORDER BY子句根据指定列按升序排列记录，如下所示的查询。

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;

输出

结果表如下所示−

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