一、连接查询简介

　　连接查询中用来连接连个表的条件称为连接条件或连接谓词。其形式为：

[<表1>].<列名1><连接运算符>[<表2>].<列2>　　

　　常见的连接运算符包括

　　1、比较运算符：=、>、<、>=、<=、!=、between和and。

　　2、逻辑运算符:not、and、or。

　　3、使用between和and连接查询形式为[<表1>].<列名1><between>[<表2>].<列名2>and[<表2>].<列名3>。

二、连接按照结果集分类

　　1、内连接：表中的行互相连接。结果集的行数等于每个表满足条件的行数的乘积，参与连接的表示平等的。

　　2、外连接：参与连接的表有主次之分，主表的每一行数据去匹配从表的数据列，符合连接条件的数据将直接返回到结果集中，不符合连接条件的数据列将以null填充后返回到结果集中，其中外连接又分左外连接、右外连接和全连接3种。

　　(一)、等值连接查询

select p.*,c.* from country as c,person as p where c.countryid = p.countryid

　　等上面的等值连接中，两张表都有countryid字段，因此查出来的结果中就会有两列countryid

　　(二)、在等值结果中消除数据就是自然连接

select p.name,c.countryname from country as c,person as p where c.countryid = p.countryid

　　(三)、自身连接

　　一个数据表自己与自己建立连接称为自身连接

三、内连接查询

　　1、内连接查询的语法结构如下：

　　　　　　select <属性或表达式列表>
　　　　　　from <表名> [inner] join <表名>
　　　　　　on <连接条件>
　　　　　　[ where <限定条件> ]

　　inner可以省略，当只见到join时就是省略了inner。内连接就是传统的连接操作，这里用on子句指定连接条件，用where子句指定其他限定条件：

　　select p.name,c.countryname from country as c inner join person p on p.countryid = c.countryid

四、左外连接查询

　　1、左外连接查询的语法结构：

　　　　select <属性或表达式列表>
　　　　from <表名> left outer join <表名>
　　　　on <连接条件>
　　　　[ where <限定条件> ]

　　如：

　　select p.name,c.countryname from country as c left join person p on p.countryid = c.countryid　

　　在结果表中包含第一个表中满足条件的所有记录，如果是在连接连接上匹配的记录，则第二个表返回相应值，否则第二个表返回null。也就是说，不管第二个表有没有记录都会第一个表的所有字段都会返回，这就是外连接与内连接的区别。

五、右外连接查询

　　1、右外连接查询的语法结构如下：

　　　　select <属性或表达式列表>
　　　　from <表名> right outer join <表名>
　　　　on　<连接条件>
　　　　[ where <限定条件> ]

　　如：

select p.name,c.countryname from country as c right join person p on p.countryid = c.countryid

　　在结果表中包含第二个表中满足条件的所有记录。如果是在连接条件上匹配的记录，则第一个表返回相应值，否则第一个表返回null。

六、全外连接查询

　　1、全外连接查询的语法结构如下:

　　　select <属性或表达式列表>
　　　from <表名> full outer join <表名>
　　　on <连接条件>
　　　where <限定条件>]

　　如：

　　select p.name,c.countryname from country as c full join person p on p.countryid = c.countryid

　　在结果表中包含两个表中满足条件的所有记录。如果是在连接条件上匹配的元组，则另一个表返回相应则，没有则返回null。

七、交叉连接

　　交叉连接Corss join，用于将第一张表的所有记录与第二张表的所有记录组合一次并返回，这个东西在生成测试数据库时很有用，例如，你定义7个姓，7个名，再交叉连接就能够产生49条记录。

　　　如有如下表：姓氏表

　　　　名字表：

　　　　执行如下SQL语句：

　　select LastName + firstname from name2 cross join name1

　　　　　　 结果如下：

8、联合查询union(union all)

　　　union是一个特殊的运算符，用于将两个或两个以上的查询产生一个结果集。join将信息水平连接(添加更多列)，而union将信息垂直连接(添加更多行)。

　　　当使用union处理查询时，要注意以下几个关键点。

　　　(1)、所有union的查询必须在select列表中有相同的列数。即如果第一个查询有3个列数，第二个查询也要只有3个列数。

　　　(2)、union返回结果的标题集仅从第一个查询中获得，无论第二个查询如何命名或取别名都不会更改。

　　　(3)、查询中对应的列的数据类型必须隐式一致。注意不要求完全一致，只需要隐式一致。

　　　(4)、与其他非union不同，union的默认返回选项为distinct，而不是all。union all语句与union的不同点仅仅在于遇到相同的记录，全部保留而已。

　　　例如还是用第7条的例子，执行如下语句：

select * from name1 
union
select * from name2

　　　返回结果为：

　　　由于union默认是distinct查询，因此想要获得所有的记录时，可以用union all，这样就算上述例子中两个表都有'王'，也会两条记录一起返回。

　　　现在来看一下综合示例：

　　　为了展示一下，建了两张表，并添加了几条记录，如下：

　　　　person表

　　　　country表

　　　1、查询一个列表，该列表要包含国家名称列表与该国家下的person总数。

select c.countryname,count(p.id) 
from country as c 
inner join person as p on c.countryid = p.countryid 
group by c.countryname

　　　　输出结果：

　　　　现在在来加一个条件，要求人口数按升序排列：

select c.countryname,count(p.id) as co
from country as c 
inner join person as p on c.countryid = p.countryid 
group by c.countryname
order by co asc

　　　　输出结果：

　　　　再加一个条件，要求只输出person数大于2的记录:

select c.countryname,count(p.id) as co
from country as c 
inner join person as p on c.countryid = p.countryid 
group by c.countryname
having count(p.id) > 2
order by co asc

　　　　输出结果为：

1）内联接：

   内联接使用比较运算符（使用像 =  或 <> 之类的比较运算符）根据每个表共有的列的值匹配两个表中的行，根据这两张表中相同列的条件，得出其      交集。例如：  检索 students和courses表中学生标识号相同的所有行。   

     有两种，显式的和隐式的，返回连接表中符合连接条件和查询条件的数据行（链接表就是数据库在做查询形成的中间表）。

      隐式的内连接：

 没有INNER JOIN，形成的中间表为两个表的笛卡尔积。   

[sql]  view plain  copy

 print ?

1. SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAME  
2. FROM CUSTOMERS C,ORDERS O  
3. WHERE C.ID=O.CUSTOMER_ID;  

一般称为内连接，有INNER JOIN，形成的中间表为两个表经过ON条件过滤后的笛卡尔积。

[sql]  view plain  copy

 print ?

1. SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAME  
2. FROM CUSTOMERS C INNER JOIN ORDERS O ON C.ID=O.CUSTOMER_ID;  

2）左向外联：

      是用的是LEFT  JOIN或LEFT OUTER JOIN     连接语句。

      根据两张表的关系（外键关联），笛卡尔过滤，也就是求出两张表的交集， 如果交集中，左边表的行， 在右边表中没有匹配，则该条记录左边表有数据， 右边表所有的字段都为null。

       左外连接时，写where语句的独立查询条件：  规则， on后面写连接条件， where后写查询条件  

[sql]  view plain  copy

 print ?

1. SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME  
2. FROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID  
3. WHERE O.ORDER_NUMBER<>'MIKE_ORDER001';  

3）右外连接：

      RIGHT  JOIN 或 RIGHT  OUTER  JOIN     连接语句

       和左外连接是相反的，查出的两张表的交集， 如果这条记录，右边表有数据，左边表没有， 则把左边表的字段都设置为null。

 案例：

[sql]  view plain  copy

 print ?

1. a表     id   name     b表     id   job   parent_id     
2.             1   张3                   1     23     1     
3.             2   李四                 2     34     2     
4.             3   王武                 3     34     4         
5. a.id同parent_id   存在关系     

  内连接查询：

[sql]  view plain  copy

 print ?

1. select   a.*,b.*   from   a   inner   join   b     on   a.id=b.parent_id        
2. 结果是       
3. 1   张3                   1     23     1     
4. 2   李四                  2     34     2     

[sql]  view plain  copy

 print ?

1. select   a.*,b.*   from   a   left   join   b     on   a.id=b.parent_id        
2. 结果是       
3. 1   张3                   1     23     1     
4. 2   李四                  2     34     2     
5. 3   王武                  null     

[sql]  view plain  copy

 print ?

1. select   a.*,b.*   from   a   right   join   b     on   a.id=b.parent_id         
2. 结果是       
3. 1   张3                   1     23     1     
4. 2   李四                  2     34     2     
5. null                       3     34     4    

对于SQL查询的基本原理：

1）单表查询：根据WHERE条件过滤表中的记录，然后根据SELECT的选择列选择相应的列进行返回最终结果。

 2）两表连接查询： 在on后面写连接条件， 在where后面写过滤的查询条件，然后再根据SELECT指定的列返回查询结果。

3）多表连接查询：先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。

关于on条件和where条件的区别：

ON条件：是过滤两个链接表笛卡尔积形成中间表的约束条件。

WHERE条件：ON只进行连接操作，WHERE只过滤中间表的记录。

对于连接类型的选择：  在实际运用中如果连接类型选择不当， 不但出现效率低并且可能还会出现逻辑的错误

1、 查两表关联列相等的数据用内连接

2、 Col_L是Col_R的子集时用右外连接。（左边表是右边表的子集，用右外）

3、 Col_R是Col_L的子集时用左外连接。（右边表是左边表的子集， 用左外）

4、 求差操作的时候用联合查询。

并且，多个表查询的时候，这些不同的连接类型可以写到一块

例如：

[sql]  view plain  copy

 print ?

1. SELECT T1.C1,T2.CX,T3.CY  
2. FROM TAB1 T1  
3.        INNER JOIN TAB2 T2 ON (T1.C1=T2.C2)  
4.        INNER JOIN TAB3 T3 ON (T1.C1=T2.C3)  
5.        LEFT OUTER JOIN TAB4 ON(T2.C2=T3.C3);  
6. WHERE T1.X >T3.Y;  

    通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，是它区别于其它类型 数据库管理系统的一个标志。连接可以在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
       查询结果：
        

参考链接：http://www.cnblogs.com/worksguo/articles/1030214.html
	http://zh.wikipedia.org/wiki/连接_(SQL) 


 







参考：http://blog.csdn.net/nieson2012/article/details/45789461





http://www.cnblogs.com/kissdodog/archive/2013/06/03/3116233.html

前面两天带着大家换了一个口味，带着大家学习了pyecharts的原理和部分图形制作。今天我们继续回归带你学MySQL系列，带着大家继续学习MySQL数据库。

多表查询是数据分析师日常工作中一定会使用到的一个知识点，可见它的重要程度有多大。今天这个文章全面总结了MySQL多表查询的几种情况，你知道MySQL的92语法和99语法吗？你知道等值连接和非等值连接吗？不要觉得某些知识点用的少，就可以不知道，其实不然，至少别人写出来后，你要知道是什么意思。

1.笛卡尔积现象

结果如下：

分析如下：
  上述结果肯定是不对的，左表中每一个人都有4个男朋友，仔细观察这4条记录，正好是左表每一条记录和右表一一匹配后的结果。
  笛卡尔积现象产生的原因：两张表没有有效的连接条件。既然你没有连接条件，本表中的第一行肯定是能和另外表中的所有行进行一一匹配，同理，本表中的第二行肯定是能和另外表中的所有行进行一一匹配，以此类推，本表中的最后一行m也可以和另外表中的所有行进行一一匹配。若另外一张表有n行，那么最后显示的行数，肯定就是m*n行了。
  如果不想产生笛卡尔积现象，就需要添加有效的表连接条件。拿上述例子来说，左表boyfriend_id只有和右边id相等时，才代表她们的男朋友。

添加表连接条件后：

可以看到，笛卡尔积最终产生的记录数是两张表中各自数据的乘积，当没有使用连接查询的时候，如果两张表中数据特别大的时候，将会撑爆你的内存，那是很可怕的，因此我们要学会使用连接查询。

2.连接查询知识点概括

1）什么是连接查询？

在实际开发中，大多数的情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结果。也就是说：实际业务中也是一个业务由多个表构成，不同的信息存储在不同的表中，如果我们想要获取的信息来自多张表，此时你就需要使用连接查询。

2）连接查询的分类

① 按年代分类

MySQL中不支持全连接(full join)，一般使用的是union关键字完成全连接的功能。MySQL中交叉连接是cross join，用的较少，我们也不用关心。

• sq192标准：仅支持内连接；
• sq199标准【推荐】：仅仅支持内连接+外连接(仅支持左外和右外)+交叉连接；

② 按功能分类

• 内连接：等值连接、非等值连接、自连接；
• 外连接：左外连接、右外连接、全外连接(full join)；

3.内连接讲解

原始数据如下：

1）等值连接：最大特点是，连接条件为等量关系。

习题：查询员工名和对应的部门名；

sql92语法如下：（太老了，一般不用，看到了知道什么意思就行。）

sql99语法：（常用的）

2）sql92语法和sql99语法的区别。

 -- sql92语法
 select ename,dname
 from emp,dept
 where emp.deptno=dept.deptno;
 
-- sql99语法
select ename,dname
from emp (inner)join dept
on emp.deptno=dept.deptno;

-- sql92语法和sql99语法的区别
1）逗号(",")换成(inner)join；
2）where换成on；
注：inner可以省略，写上inner可以增加代码的可读性。

--sql99语法的优势
表连接和后面的where条件筛选，分离开来。
对于sql92语法来说，表连接用的是where，where筛选用的也是where，混淆在一起不清不楚。

3）非等值连接：最大特点是，连接条件为非等量关系。

习题：找出每个员工的工资等级，要求显示员工名、工资、工资等级。

4）自连接：最大特点是，一张表看作两张表。

什么叫做一张表看作两张表呢？也就是说，自连接是同一张表之间的连接，连接条件就是这张表中的不同字段。

人和机器的最大不同，就在于人有判断能力，你知道区分使用一张表的不同字段，但是机器不知道，都是同一张表，字段名也都是相同的。那么，机器怎么才能区分哪个表是哪个表(对于同一张表来说)。

这就需要起别名了。对于同一张表来说，我给它取两个名字，一个是A，一个是B，这样机器就能很好的区分了。当取A表中的字段，就是"A.字段"，取B表中的字段，就是"B.字段"。

习题：找出每个员工的上级领导，要求显示员工名和对应的领导名。

4.外连接讲解

原始数据如下：

1）什么是外连接，和内连接有什么区别？

① 内连接

假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录，就会查询出来，这就是内连接。AB两张表没有主副之分，两张表是平等的。

② 外连接

假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表。当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。

外连接最重要的特点是：主表的数据,无条件的全部查询出来。

2）外连接的分类

左连接有右连接的写法，右连接也会有对应的左连接的写法。因此，我们在学习MySQL的过程中，没有必要既学习左连接又学习右连接。

• 左外连接（左连接）：表示左边的这张表是主表。
• 右外连接（右连接）：表示右边的这张表是主表。

3）案例分析

习题：找出哪个部门没有员工？

总结：内连接就是两个表的交集 ,左外连接就是左边表加两表交集 ,右外连接就是右边表加两表交集

一、外连接

1.左连接  left join 或 left outer join

SQL语句：select * from student left join score on student.Num=score.Stu_id;

2.右连接  right join 或 right outer join

SQL语句：select * from student right join score on student.Num=score.Stu_id;

3.完全外连接  full join 或 full outer join

SQL语句：select * from student full join score on student.Num=score.Stu_id;

通过上面这三种方法就可以把不同的表连接到一起，变成一张大表，之后的查询操作就简单一些了。

交叉连接查询,这种查询方式基本不会使用，原因就是这种查询方式得到的是两个表的乘积（笛卡儿集）

语法就是select * from a，b；则尽量不使用此语句，产生的结果过于繁琐。

内连接查询，可以有效的去除笛卡尔集现象

内连接查询分为两类：

二、内连接 

join 或 inner join

SQL语句：select * from student inner join score on student.Num=score.Stu_id;

此时的语句就相当于：select * from student,score where student.ID=course.ID;

内连接查询分为两类：

隐式内连接

select * from A，B where 条件                                          隐式连接使用别名：

select * from A 别名1，B 别名2 where 别名1.xx=别名2.xx；

显示内连接

select * from A inner join B on 条件 （inner可以省略）  显示连接使用别名：

select * from A 别名1 inner join B 别名2 on 别名1.xx=别名2.xx

举例：

三、交叉连接

cross join，没有where指定查询条件的子句的交叉联接将产生两表的笛卡尔积。

SQL语句：select * from student cross join score;