表连接总结

通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，也是它区别于其它类型数据库管理系统的一个标志。连接类型可分为三种：内连接、外连接和交叉连接。下面分别介绍连接类型及特点。

 

实例表
buyer (buyer_id, buyer_name)
sales (sales_id, buyer_id, qty)

 

一、内连接（INNER JOIN）
需要使用比较运算符（即需要ON关键字及条件）; 列出两个表中所有匹配的的记录(等值连接时查询结果只显示可以匹配的记录);
 举例:
 buyer:
 (1, N1)
 (2, N1)
 (3, N1)
 (4, N1)
 sales:
 (1, 1, 100)
 (2, 2, 99)
 (3, 3, 98)
 (4, 4, 97)
 (5, 5, 96)
 (6, 1, 23)
 等值连接时结果只有5条记录;(4, 5, 96)因为没有与之关联的项目不出现在查询结果;同样如果buyer中增加一条(10, N10)记录,sales表中没有与之对应的项目同样不出现在查询结果.

 内连接分为三种：
 a、等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。
 b、不等连接： 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。 (当不等连接时使用笛卡尔积计算结果)
 c、自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。
 使用自然连接，在选择列表中删除authors 和publishers 表中重复列(city和state)：
　　 SELECT a.*,p.pub_id,p.pub_name,p.country
　　 FROM authors AS a INNER JOIN publishers AS p
　　 ON a.city=p.city

二、外连接（LEFT OUTER JOIN,或LEFT JOIN, RIGHT OUTER JOIN,或RIGHT JOIN, FULL OUTER JOIN,或FULL JOIN）
需要使用比较运算符;
 a、左连接时(连接条件中使用等于号)显示左表与左表可以关联的所有记录+左边没有与右表关联的记录(这时有表的字段显示为NULL)
 举例:
 buyer:
 (1, N1)
 (2, N1)
 (3, N1)
 (10, N10)
 sales:
 (1, 1, 100)
 (2, 2, 99)
 (3, 3, 98)
 (4, 4, 97)
 (5, 2, 96)
 (6, 1, 23)
 结果:
 (1, N1)(1, 1, 100)
 (1, N1)(6, 1, 23)
 (2, N1)(5, 2, 96)
 (2, N1)(2, 2, 99)
 (3, N1)(3, 3, 98)
 (10, N10)(NULL, NULL, NULL)
 b、右连接时(连接条件中使用等于号)显示左表与右表可以关联的所有记录+右边没有与右表关联的记录(这时有表的字段显示为NULL)
 举例:
 buyer:
 (1, N1)
 (2, N1)
 (3, N1)
 (10, N10)
 sales:
 (1, 1, 100)
 (2, 2, 99)
 (3, 3, 98)
 (4, 4, 97)
 (5, 2, 96)
 (6, 1, 23)
 (7, 5, 97)
 结果:
 (1, N1)(1, 1, 100)
 (1, N1)(6, 1, 23)
 (2, N1)(5, 2, 96)
 (2, N1)(2, 2, 99)
 (3, N1)(3, 3, 98)
 (NULL, NULL)(4, 4, 97)
 (NULL, NULL)(7, 5, 97)
 c、完整连接时,不管另一个表是否有匹配的值，此运算符都包括两个表中的所有行。
 举例:
 buyer:
 (1, N1)
 (2, N1)
 (3, N1)
 (10, N10)
 sales:
 (1, 1, 100)
 (2, 2, 99)
 (3, 3, 98)
 (4, 4, 97)
 (5, 2, 96)
 (6, 1, 23)
 (7, 5, 97)
 结果:
 (1, N1)(1, 1, 100)
 (1, N1)(6, 1, 23)
 (2, N1)(5, 2, 96)
 (2, N1)(2, 2, 99)
 (3, N1)(3, 3, 98)
 (10, N10)(NULL, NULL, NULL)
 (NULL, NULL)(4, 4, 97)
 (NULL, NULL)(7, 5, 97)

三、交叉连接（CROSS JOIN）
不能使用比较运算符;它返回被连接的两个表所有数据行的笛卡尔积，返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。例，titles表中有6类图书，而publishers表中有8家出版社，则下列交叉连接检索到的记录数将等于6*8=48行。

 

四、注意点
a、SQL使用OUTER JOIN查询，使用WHERE与AND的区别。
  join的原理是先执行on后面条件对表进行筛选；然后连接两个表；最后对连接后的结果使用where条件进行筛选。

 

五、应用
a、使用自连接查询最大值或最小值
  最小值：select distinct t1.qty from sales as t1 where not exists
(select t2.buyer_id from sales as t2 where t1.buyer_id = t2.buyer_id
and t1.qty > t2.qty)
  最大值：select distinct t1.qty from sales as t1 where not exists
(select t2.buyer_id from sales as t2 where t1.buyer_id = t2.buyer_id
and t1.qty > t2.qty)