SQL连接查询总结和练习

通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，是它区别于其它类型 数据库管理系统的一个标志。连接可以在SELECT 语句的FROM子句或WHERE子句中建立，在FROM子句中指出连接时有助于将连接操作与WHERE子句中的搜索条件区分开来。一般来说，连接查询比嵌套查询的效率高一点。所以，在Transact-SQL中推荐使用这种方法。
 
 

  
  　　 SQL-92标准所定义的FROM子句的连接语法格式为：
　　　　 FROM join_table join_type join_table [ON (join_condition)]
　　其中join_table指出参与连接操作的表名，连接可以对同一个表操作，也可以对多表操作，对同一 个表操作的连接又称做自连接。
  
  join_type 指出连接类型，可分为内连接和外连接。
 
 
 
 

  
  	
 
 
 
 

  
  	练习数据表：
 
 
 
 

  
  		表一：press(出版社）
 
 
 
 

  
  			
 
 
 
 

  
  		表二：authors（作者）
 
 
 
 

  
  			
 
 
一、内连接（INNER JOIN）
 
 

  
      内连接是应用程序中用的普遍的"连接"操作，它一般都是默认连接类型。内连接基于连接谓词将两张表(如 A 和 B)的列组合在一起，产生新的结果表。查询会将 A 表的每一行和 B 表的每一行进行比较，并找出满足连接谓词的组合。当连接谓词被满足，A 和 B 中匹配的行会按列组合(并排组合)成结果集中的一行。连接产生的结果集，可以定义为首先对两张表做笛卡尔积(交叉连接) -- 将 A 中的每一行和 B 中的每一行组合，然后返回满足连接谓词的记录。实际上 SQL 产品会尽可能用其他方式去实现连接，笛卡尔积运算是非常没效率的. 
 
 
 
 

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

  
        表之间的连接是通过相等的字段值连接起来的查询称为等值连接查询。在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。特别注意连接依据的列可能包含 NULL 值，NULL 值不与任何值匹配(甚至和它本身)
 
 
 
 

  
        例如：列出authors和press表中位于同一城市的作者和出版社
 
 
 
 

  
        SELECT * FROM press AS p INNER JOIN authors AS a ON p.city=a.city 
 
 
 
 

  
         等价于：SELECT * FROM press AS p ,authors AS a WHERE p.city=a.city
 
 
 
 

  
            查询结果：
 
 
 
 

  
  		
 
 
    2）非等值连接                      
 
 

  
        在等值查询的连接条件中不使用等号，而使用其它比较运算符就构成了非等值连接查询。可以使用的比较运算符有：> 、>、=、 <、 <=、 !=， 还可以使用BETWEEN…AND 之类的谓词。
 
 
 
 

  
        例如：SELECT * FROM press AS p INNER JOIN authors AS a ON p.id > a.press_id   
 
 
 
 

  
            查询结果：

 
 
 
 

  
      
  
      
  
      
  
  
  
  		
 
 
  2、自然连接
 
 

  
        自然连接(Natural join)是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性组，两表中的所有名称相同的列都将被比较，并且在结果中把重复的属性列去掉，结果表中两表中名称相同的列只出现一次.。而等值连接并不去掉重复的属性列。（sqlserver 不支持 自然连接）
 
 
 
 

  
        例如：SELECT * FROM authors  NATRUAL JOIN press 
 
 
 
 

  
  	查询结果与等值连接一样，只是去掉了重复的id那一列。
 
 
  3、交叉连接
 
 

  
      交叉连接(cross join)，又称笛卡尔连接(cartesian join)或叉乘(Product)，它是所有类型的内连接的基础。把表视为行记录的集合，交叉连接即返回这两个集合的笛卡尔积，返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。这其实等价于内连接的链接条件为"永真"，或连接条件不存在.。
 
 
    如果 A 和 B 是两个集合，它们的交叉连接就记为: A × B.
    例如： SELECT * FROM press CROSS JOIN authors    
    等价于：SELECT * FROM press,authors 
        查询结果:
            
二、外连接
    外连接并不要求连接的两表的每一条记录在对方表中都一条匹配的记录. 连接表保留所有记录 -- 甚至这条记录没有匹配的记录也要保留. 外连接可依据连接表保留左表, 右表或全部表的行而进一步分为左外连接, 右外连接和全连接.在标准的 SQL 语言中, 外连接没有隐式的连接符号.
  1、左外连接
    左外连接会返回左表的所有记录和右表中匹配记录的组合(如果右表中无匹配记录, 来自于右表的所有列的值设为 NULL). 如果左表的一行在右表中存在多个匹配行, 那么左表的行会复制和右表匹配行一样的数量, 并进行组合生成连接结果.
    例如：SELECT *  FROM   press LEFT OUTER JOIN authors ON press.id = authors.press_id
    查询结果：
	
      

2、右外连接
    右外连接, 亦简称右连接, 它与左外连接完全类似, 只不过是作连接的表的顺序相反而已.右连接操作返回右表的所有行和这些行在左表中匹配的行(没有匹配的, 来源于左表的列值设为 NULL).






 
 

  
  
    例如：SELECT * FROM press RIGHT OUTER JOIN authors ON press.id= authors.press_id
  
  
        等价于：SELECT * FROM authors LEFT OUTER JOIN press ON press.id= authors.press_id

  
  
        查询结果：

  
  
      		
 		
  
  
    实际上显式的右连接很少使用, 因为它总是可以被替换成左连接--换换表的位置就可以了，所以上面两条语句是等价的。
  
  

  
  


  
  

3、全连接

  
  
    全连接是左右外连接的并集. 连接表包含被连接的表的所有记录, 如果缺少匹配的记录, 即以 NULL 填充.。一些数据库系统(如 MySQL)并不直接支持全连接, 但它们可以通过左右外连接的并集(参: union)来模拟实现

 
 

 
 

  
  
    例如：SELECT * FROM   press FULL OUTER JOIN authors ON press.id= authors.press_id

  
  
     查询结果：
  
  
      
	


  
  

  
  
三、自连接
  
  
     自身连接是指同一个表自己与自己进行连接。既可以用内连接，也可以用外连接。

  
  
     例如：SELECT * FROM   authors a1 LEFT JOIN authors a2 ON a2.press_id = a1.id
       查询结果：