自然连接

自然连接是等值连接的一种，构建它来使连接列不会多余地显示数据，如以下查询所示。

图: 查询

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

类似等值连接的示例，该查询在 manu_code 列上连接 manufact 和 stock 表。因为更接近地定义了投影列表，所以只对检索到的每一行列出一次 manu_code ，如下所示。

图: 查询结果

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_name    Smith
          lead_time       3
          stock_num    5
          manu_code    SMT
          description  tennis racquet
          unit_price   $25.00
          unit         each
          unit_descr   each
          
          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_name    Anza
          lead_time       5
          stock_num    5
          manu_code    ANZ
          description  tennis racquet
          unit_price   $19.80
          unit         each
          unit_descr   each
          ⋮

所有的连接都是相关联的。即，WHERE 子句中的连接术语不影响连接的意义。

下列查询中的两个语句都创建相同的自然连接。

图: 查询

SELECT catalog.*, description, unit_price, unit, unit_descr
          FROM catalog, stock
          WHERE catalog.stock_num = stock.stock_num
          AND catalog.manu_code = stock.manu_code
          AND catalog_num = 10017;
          
          SELECT catalog.*, description, unit_price, unit, unit_descr
          FROM catalog, stock
          WHERE catalog_num = 10017
          AND catalog.manu_code = stock.manu_code
          AND catalog.stock_num = stock.stock_num;

每个语句检索到下列行。

图: 查询结果

catalog_num  10017
        stock_num    101
        manu_code    PRC
        cat_descr    
        Reinforced, hand-finished tubular. Polyurethane belted. 
        Effective against punctures. Mixed tread for super wear 
        and road grip.
        cat_picture  <BYTE value>
        
        cat_advert   Ultimate in Puncture Protection, Tires 
        Designed for In-City Riding
        description  bicycle tires
        unit_price   $88.00
        unit         box
        unit_descr   4/box

图 3包括 TEXT 列 cat_descr 、BYTE 列 cat_picture 和 VARCHAR 列 cat_advert。