自连接

连接不一定总是涉及两个不同的表，可以将表连接至它本身，创建自连接。当想要将列中的值与同一列中的其他值进行比较时，将表连接至它本身非常有用。

要创建自连接，在 FROM 子句中列出表两次，并且每次为它指定不同的别名。使用别名在 Projection 和 WHERE 子句中引用表。如同是两个独立的表一样。（SELECT 语句中的别名在别名和 GBase 8s SQL 指南：语法中讨论。）

与表之间的连接一样，可以在自连接中使用算术表达式，可以测试空值。可以使用 ORDER BY 子句来以升序或降序对指定列中的值进行排序。

下列查询查询 ship_weight 相差五倍或更多并且 ship_date 不为空的订单。接着，查询按照 ship_date 对数据进行排序。

图: 查询

SELECT x.order_num, x.ship_weight, x.ship_date,
          y.order_num, y.ship_weight, y.ship_date 
          FROM orders x, orders y
          WHERE x.ship_weight >= 5 * y.ship_weight
          AND x.ship_date IS NOT NULL
          AND y.ship_date IS NOT NULL
          ORDER BY x.ship_date;

表 1. 查询结果
order_num	ship_weight	ship_date	order_num	ship_weight	ship_date
1004	95.80	05/30/1998	1011	10.40	07/03/1998
1004	95.80	05/30/1998	1020	14.00	07/16/1998
1004	95.80	05/30/1998	1022	15.00	07/30/1998
1007	125.90	06/05/1998	1015	20.60	07/16/1998
1007	125.90	06/05/1998	1020	14.00	07/16/1998

如果想要将自连接的结果存储到临时表中，那么将 INTO TEMP 子句追加到 SELECT 语句中，并至少对一组列指定显示标号，以重命名这些列。否则，重复列名将导致错误，并且不会创建临时表。

下列查询，类似于图 1，标记从 orders 表选择的所有列，并将这些列放置在称为 shipping 的临时表中。

图: 查询

SELECT x.order_num orders1, x.po_num purch1, 
          x.ship_date ship1, y.order_num orders2, 
          y.po_num purch2, y.ship_date ship2
          FROM orders x, orders y
          WHERE x.ship_weight >= 5 * y.ship_weight
          AND x.ship_date IS NOT NULL
          AND y.ship_date IS NOT NULL
          ORDER BY orders1, orders2
          INTO TEMP shipping;

如果您从表 shipping 中查询 SELECT *，可以看到下列行。

图: 查询结果

    orders1 purch1     ship1          orders2 purch2    ship2
          
          1004 8006       05/30/1998        1011 B77897    07/03/1998
          1004 8006       05/30/1998        1020 W2286     07/16/1998
          1004 8006       05/30/1998        1022 W9925     07/30/1998
          1005 2865       06/09/1998        1011 B77897    07/03/1998
          ⋮
          1019 Z55709     07/16/1998        1020 W2286     07/16/1998
          1019 Z55709     07/16/1998        1022 W9925     07/30/1998
          1023 KF2961     07/30/1998        1011 B77897    07/03/1998

可以多次将表连接至它本身。自连接的最大次数取决于您可用的资源。

下列查询中的自连接在 stock 表中创建由三个制造商供货的那些商品的列表。自连接在 WHERE 子句中包括最后两个条件，来除去行中检索到的重复的制造商代码。

图: 查询

SELECT s1.manu_code, s2.manu_code, s3.manu_code, 
          s1.stock_num, s1.description
          FROM stock s1, stock s2, stock s3
          WHERE s1.stock_num = s2.stock_num
          AND s2.stock_num = s3.stock_num
          AND s1.manu_code < s2.manu_code
          AND s2.manu_code < s3.manu_code
          ORDER BY stock_num;

图: 查询结果

manu_code manu_code manu_code stock_num description
            
            HRO       HSK       SMT               1 baseball gloves
            ANZ       NRG       SMT               5 tennis racquet 
            ANZ       HRO       HSK             110 helmet
            ANZ       HRO       PRC             110 helmet
            ANZ       HRO       SHM             110 helmet
            ANZ       HSK       PRC             110 helmet
            ANZ       HSK       SHM             110 helmet
            ANZ       PRC       SHM             110 helmet
            HRO       HSK       PRC             110 helmet
            HRO       HSK       SHM             110 helmet
            HRO       PRC       SHM             110 helmet
            ⋮
            KAR       NKL       PRC             301 running shoes
            KAR       NKL       SHM             301 running shoes
            KAR       PRC       SHM             301 running shoes
            NKL       PRC       SHM             301 running shoes

如果想要从 payroll 表选择行来确定哪些职员的薪水高于他们的经理，可以按以下 SELECT 语句所示构造自连接：

SELECT emp.employee_num, emp.gross_pay, emp.level, 
          emp.dept_num, mgr.employee_num, mgr.gross_pay, 
          mgr.dept_num, mgr.level
          FROM payroll emp, payroll mgr
          WHERE emp.gross_pay > mgr.gross_pay 
          AND emp.level < mgr.level
          AND emp.dept_num = mgr.dept_num
          ORDER BY 4;

下列查询使用相关子查询来检索并列出预订的 10 种价格最高的商品。

图: 查询

SELECT order_num, total_price 
          FROM items a
          WHERE 10 >
          (SELECT COUNT (*) 
          FROM items b
          WHERE b.total_price < a.total_price)
          ORDER BY total_price;

该查询返回 10 行。

图: 查询结果

    order_num    total_price
        
        1018      $15.00
        1013      $19.80
        1003      $20.00
        1005      $36.00
        1006      $36.00
        1013      $36.00
        1010      $36.00
        1013      $40.00
        1022      $40.00
        1023      $40.00

可以创建类似的查询来查找并列出公司中资格最老的 10 个职员。

有关相关子查询的更多信息，请参阅SELECT 语句中的子查询。