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 );

表将会被创建成−

现在让我们使用以下自连接查询来加入这个表。我们的目标是根据他们的收入建立客户之间的关系。我们正在使用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;

输出：

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

自连接和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表 –

在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的数字顺序等进行排序。