SQL 多表联合查询

很少用join，这次学学，并备忘两篇文章！

 

转自：http://hcx-2008.javaeye.com/blog/285661

 

连接查询

通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，也是它区别于其它类型数据库管理系统的一个标志。

在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当检索数据时，通过连接操作查询出存放在多个表中的不同实体的信息。连接操作给用户带来很大的灵活性，他们可以在任何时候增加新的数据类型。为不同实体创建新的表，尔后通过连接进行查询。

连接可以在SELECT 语句的FROM子句或WHERE子句中建立，似是而非在FROM子句中指出连接时有助于将连接操作与WHERE子句中的搜索条件区分开来。所以，在Transact-SQL中推荐使用这种方法。

SQL-92标准所定义的FROM子句的连接语法格式为：

FROM join_table join_type join_table

[ON (join_condition)]

其中join_table指出参与连接操作的表名，连接可以对同一个表操作，也可以对多表操作，对同一个表操作的连接又称做自连接。

join_type 指出连接类型，可分为三种：内连接、外连接和交叉连接。内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。根据所使用的比较方式不同，内连接又分为等值连接、自然连接和不等连接三种。

外连接分为左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN或RIGHT JOIN)和全外连接(FULL OUTER JOIN或FULL JOIN)三种。与内连接不同的是，外连接不只列出与连接条件相匹配的行，而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。

交叉连接(CROSS JOIN)没有WHERE 子句，它返回连接表中所有数据行的笛卡尔积，其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。

连接操作中的ON (join_condition) 子句指出连接条件，它由被连接表中的列和比较运算符、逻辑运算符等构成。

无论哪种连接都不能对text、ntext和image数据类型列进行直接连接，但可以对这三种列进行间接连接。例如：

SELECT p1.pub_id,p2.pub_id,p1.pr_info

FROM pub_info AS p1 INNER JOIN pub_info AS p2

ON DATALENGTH(p1.pr_info)=DATALENGTH(p2.pr_info)



(一)内连接

内连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。内连接分三种：

1、等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。

2、不等连接： 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。

3、自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。

例，下面使用等值连接列出authors和publishers表中位于同一城市的作者和出版社：

SELECT *

FROM authors AS a INNER JOIN publishers AS p

ON a.city=p.city



又如使用自然连接，在选择列表中删除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



(二)外连接

内连接时，返回查询结果集合中的仅是符合查询条件( WHERE 搜索条件或 HAVING 条件)和连接条件的行。而采用外连接时，它返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。

如下面使用左外连接将论坛内容和作者信息连接起来：

SELECT a.*,b.* FROM luntan LEFT JOIN usertable as b

ON a.username=b.username



下面使用全外连接将city表中的所有作者以及user表中的所有作者，以及他们所在的城市：

SELECT a.*,b.*

FROM city as a FULL OUTER JOIN user as b

ON a.username=b.username



(三)交叉连接

交叉连接不带WHERE 子句，它返回被连接的两个表所有数据行的笛卡尔积，返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。

例，titles表中有6类图书，而publishers表中有8家出版社，则下列交叉连接检索到的记录数将等

于6*8=48行。

SELECT type,pub_name

FROM titles CROSS JOIN publishers

ORDER BY type

 

转自：http://topic.csdn.net/t/20060809/09/4936637.html

12 楼aw511（点点星灯）回复于 2006-08-22 09:13:42 得分 0 --查询分析器中执行：  

 
  --建表table1,table2：  
  create   table   table1(id   int,name   varchar(10))  
  create   table   table2(id   int,score   int)  
  insert   into   table1   select   1,'lee'  
  insert   into   table1   select   2,'zhang'  
  insert   into   table1   select   4,'wang'  
  insert   into   table2   select   1,90  
  insert   into   table2   select   2,100  
  insert   into   table2   select   3,70  
  如表  
  -------------------------------------------------  
  table1|table2|  
  -------------------------------------------------  
  idname|idscore|  
  1lee|190|  
  2zhang|2100|  
  4wang|370|  
  -------------------------------------------------  
   
  以下均在查询分析器中执行  
   
  一、外连接  
  1.概念：包括左向外联接、右向外联接或完整外部联接  
   
  2.左连接：left   join   或   left   outer   join  
  (1)左向外联接的结果集包括   LEFT   OUTER   子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值(null)。  
  (2)sql语句  
  select   *   from   table1   left   join   table2   on   table1.id=table2.id  
  -------------结果-------------  
  idnameidscore  
  ------------------------------  
  1lee190  
  2zhang2100  
  4wangNULLNULL  
  ------------------------------  
  注释：包含table1的所有子句，根据指定条件返回table2相应的字段，不符合的以null显示  
   
  3.右连接：right   join   或   right   outer   join  
  (1)右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。  
  (2)sql语句  
  select   *   from   table1   right   join   table2   on   table1.id=table2.id  
  -------------结果-------------  
  idnameidscore  
  ------------------------------  
  1lee190  
  2zhang2100  
  NULLNULL370  
  ------------------------------  
  注释：包含table2的所有子句，根据指定条件返回table1相应的字段，不符合的以null显示  
   
  4.完整外部联接:full   join   或   full   outer   join    
  (1)完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。  
  (2)sql语句  
  select   *   from   table1   full   join   table2   on   table1.id=table2.id  
  -------------结果-------------  
  idnameidscore  
  ------------------------------  
  1lee190  
  2zhang2100  
  4wangNULLNULL  
  NULLNULL370  
  ------------------------------  
  注释：返回左右连接的和（见上左、右连接）  
   
  二、内连接  
  1.概念：内联接是用比较运算符比较要联接列的值的联接  
   
  2.内连接：join   或   inner   join    
   
  3.sql语句  
  select   *   from   table1   join   table2   on   table1.id=table2.id  
  -------------结果-------------  
  idnameidscore  
  ------------------------------  
  1lee190  
  2zhang2100  
  ------------------------------  
  注释：只返回符合条件的table1和table2的列  
   
  4.等价（与下列执行效果相同）  
  A:select   a.*,b.*   from   table1   a,table2   b   where   a.id=b.id  
  B:select   *   from   table1   cross   join   table2   where   table1.id=table2.id     (注：cross   join后加条件只能用where,不能用on)  
   
  三、交叉连接(完全)  
   
  1.概念：没有   WHERE   子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。（table1和table2交叉连接产生3*3=9条记录）  
   
  2.交叉连接：cross   join   (不带条件where...)  
   
  3.sql语句  
  select   *   from   table1   cross   join   table2  
  -------------结果-------------  
  idnameidscore  
  ------------------------------  
  1lee190  
  2zhang190  
  4wang190  
  1lee2100  
  2zhang2100  
  4wang2100  
  1lee370  
  2zhang370  
  4wang370  
  ------------------------------  
  注释：返回3*3=9条记录，即笛卡尔积  
   
  4.等价（与下列执行效果相同）  
  A:select   *   from   table1,table2