自然连接是等值连接的一种,构建它来使连接列不会多余地显示数据,如以下查询所示。
查询
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。




