等值连接

等值连接是基于相等或匹配列值的连接。在 WHERE 子句中，使用作为比较运算符的等号（=）来表示这一相等关系。如下所示。

图: 查询

SELECT * FROM manufact, stock
          WHERE manufact.manu_code = stock.manu_code;

该查询在 manu_code 列上连接 manufact 和 stock 表。它只检索两个列的值相等的那些行。以下结果显示了一些这样的行。

图: 查询结果

manu_code    SMT
            manu_name    Smith
            lead_time       3
            stock_num    1
            manu_code    SMT
            description  baseball gloves
            unit_price   $450.00
            unit         case
            unit_descr   10 gloves/case
            
            manu_code    SMT
            manu_name    Smith
            lead_time       3
            stock_num    5
            manu_code    SMT
            description  tennis racquet
            unit_price   $25.00
            unit         each
            unit_descr   each
            
            manu_code    SMT
            manu_name    Smith
            lead_time       3
            stock_num    6
            manu_code    SMT
            description  tennis ball
            unit_price   $36.00
            unit         case
            unit_descr   24 cans/case
            
            manu_code    ANZ
            manu_name    Anza
            lead_time       5
            stock_num    5
            manu_code    ANZ
            description  tennis racquet
            unit_price   $19.80
            unit         each
            unit_descr   each
            ⋮

在等值连接中，该结果同时包括 manufact 和 stock 表中的 manu_code 列，原因是选择列表请求每个列。

还可以使用附加约束创建等值连接，此时比较条件基于连接列中值的不相等性。这些连接在 WHERE 子句中指定的比较条件中除等号（=）之外还使用其他关系运算符。

要连接包含相同名称的列的表，用列的表名和句点（.）限定每个列名，如下列查询所示。

图: 查询

SELECT order_num, order_date, ship_date, cust_calls.*
          FROM orders, cust_calls
          WHERE call_dtime >= ship_date
          AND cust_calls.customer_num = orders.customer_num
          ORDER BY orders.customer_num;

该查询连接 customer_num 列，然后只选择 cust_calls 表中 call_dtime 大于或等于 orders 表中的 ship_date 那些行。该结果显示它返回的组合行。

图: 查询结果

order_num      1004
          order_date     05/22/1998
          ship_date      05/30/1998
          customer_num   106
          call_dtime     1998-06-12 08:20
          user_id        maryj
          call_code      D
          call_descr     Order received okay, but two of the cans of                 
          ANZ tennis balls within the case were empty
          res_dtime      1998-06-12 08:25
          res_descr      Authorized credit for two cans to customer,
          issued apology. Called ANZ buyer to report 
          the qa problem.
          
          order_num      1008
          order_date     06/07/1998
          ship_date      07/06/1998
          customer_num   110
          call_dtime     1998-07-07 10:24
          user_id        richc
          call_code      L
          call_descr     Order placed one month ago (6/7) not received.
          res_dtime      1998-07-07 10:30
          res_descr      Checked with shipping (Ed Smith). Order out 
          yesterday-was waiting for goods from ANZ. 
          Next time will call with delay if necessary.
          
          order_num      1023
          order_date     07/24/1998
          ship_date      07/30/1998
          customer_num   127
          call_dtime     1998-07-31 14:30
          user_id        maryj
          call_code      I
          call_descr     Received Hero watches (item # 304) instead 
          of ANZ watches
          res_dtime      
          res_descr      Sent memo to shipping to send ANZ item 304 
          to customer and pickup HRO watches. Should 
          be done tomorrow, 8/1