/*
 左连接：
 第一种结果：A
 select 字段列表
 from A表 left join B表
 on 关联条件
 
第二种结果：A - A∩B
 select 字段列表
 from A表 left join B表
 on 关联条件
 where 从表的关联字段 is null
 
左连接和右连接的区别：
 （1）left换成right
 （2）以左边的表为主还是以右边的表为主
 右连接
 第一种结果：B
 select 字段列表
 from A表 right join B表
 on 关联条件
 
第二种结果：B - A∩B
 select 字段列表
 from A表 right join B表
 on 关联条件
 where 从表的关联字段 is null
 */
 
#查询所有员工和他的部门编号，部门名称，包括那些没有部门的员工
 SELECT *
 FROM t_employee LEFT JOIN t_department
 ON t_employee.did = t_department.did
 
#查询所有没有部门的员工
 #不用关联查询也可以实现
 SELECT * FROM t_employee WHERE did IS NULL
 
#用关联查询
 SELECT *
 FROM t_employee LEFT JOIN t_department
 ON t_employee.did = t_department.did
 WHERE t_employee.did IS NULL

获取 boy 、girl 表下载地址
 
一、内连接查询  inner join
 
关键字：inner  join   on 
 
语句：select * from a_table a inner join b_table b on a.a_id = b.b_id;
 
说明：组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。
 
 
案例解释：在boy表和girl 表中查出两表 hid 字段一致的姓名（gname，bname），boy表和girl 表如下：
 
      
 
采用内连接查询方式：
 
SELECT boy.hid,boy.bname,girl.gname FROM boy INNER JOIN girl ON girl.hid = boy.hid;
 
查询结果如下：
 
 
二、左连接查询 left join
 
关键字：left join on / left outer join on 
 
语句：SELECT  * FROM a_table a left join b_table b ON a.a_id = b.b_id;
 
说明： left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。 左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。
 
 
案例解释：在boy表和girl 表中左连接查询，boy表和girl 表如下：
 
      
 
采用内连接查询方式：
 
SELECT boy.hid,boy.bname,girl.gname FROM boy LEFT JOIN girl ON girl.hid = boy.hid;
 
查询结果如下：
 
 
三、右连接 right join
 
关键字：right join on / right outer join on
 
语句：SELECT  * FROM a_table a right outer join b_table b on a.a_id = b.b_id;
 
说明：right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。
 
 
案例解释：在boy表和girl 表中右连接查询，boy表和girl 表如下：
 
      
 
采用内连接查询方式：
 
SELECT boy.hid,boy.bname,girl.gname FROM boy RIGHT JOIN girl ON girl.hid = boy.hid;
 
查询结果如下：
 
 
四、全连接 union
 
关键字：union /union all
 
语句：(select colum1,colum2...columN from tableA ) union (select colum1,colum2...columN from tableB )
 
         或 (select colum1,colum2...columN from tableA ) union all (select colum1,colum2...columN from tableB )；
 
union语句注意事项：
 
         1.通过union连接的SQL它们分别单独取出的列数必须相同；
 
         2.不要求合并的表列名称相同时，以第一个sql 表列名为准；
 
         3.使用union 时，完全相等的行，将会被合并，由于合并比较耗时，一般不直接使用 union 进行合并，而是通常采用union all 进行合并；
 
         4.被union 连接的sql 子句，单个子句中不用写order by ，因为不会有排序的效果。但可以对最终的结果集进行排序；
 
           (select id,name from A order by id) union all (select id,name from B order by id); //没有排序效果
 
           (select id,name from A ) union all (select id,name from B ) order by id; //有排序效果
 
案例解释：将a表和b表合并，表结构如下：
 
          
 
采用 union 全连接：
 
 
union会自动将完全重复的数据去除掉，a、b表中"c"的值都为15，所以只显示一行。
 
采用 union all 全连接：
 
 
union all会保留那些重复的数据；
 
左右连接练习题：
 
根据给出的表结构按要求写出SQL语句。t 表(即Team表)和 m 表(即Match表) 的结构如下：
 
     
 
t 表(即Team表)和 m 表(即Match表) 的内容如下：
 
         
 
t 表和 m 表下载地址
 
m 表(即Match表) 的 hostTeamID 与 guestTeamID 都与 t 表(即Team表) 中的 teamID 关联。请查出 2006-6-1 到2006-7-1之间举行的所有比赛，并且用以下形式列出： 拜仁   2：0  不来梅  2006-6-21
 
===============================================================================
 
解决方案：
 
第一步：先以 m 表左连接 t 表，查出 m 表中 hid 这列对应的比赛信息:
 
SELECT m.mid,t.tname,m.mres,m.matime as time FROM m LEFT JOIN t ON t.tid = m.hid;
 
查询结果记为结果集 t1 ，t1 表如下：
 
 
第二步：先以 m 表左连接 t 表，查出 m 表中 gid 这列对应的比赛信息:
 
SELECT m.mid,t.tname,m.mres,m.matime FROM m LEFT JOIN t ON t.tid = m.gid;
 
查询结果记为结果集 t2 ，t2 表如下：
 
 
第三步：以结果集 t1 为基础左连接查询结果集 t2，查询条件为两者比赛序号（mid）相同。
 
SELECT t1.tname,t1.mres,t2.tname,t1.time FROM 
(SELECT m.mid,t.tname,m.mres,m.matime as time FROM m LEFT JOIN t ON t.tid = m.hid) 
as t1 
LEFT JOIN 
(SELECT m.mid,t.tname,m.mres,m.matime as time FROM m LEFT JOIN t ON t.tid = m.gid) 
as t2 
ON t1.mid = t2.mid WHERE t1.time BETWEEN '2006-06-01' AND '2006-07-01';
 
查询结果如下：
 
 
全连接练习题：
 
A表和B表结构如下，请将两表合并：
 
            
 
合并要求：A表中a:5，B表中a：5，因此合并后表中a对应的值为10；要求查出的结果样本如下：
 
 
采用 union all 全连接，然后使用from 子查询：
 
SELECT id,SUM(num) as num FROM ((SELECT id,num FROM a) UNION ALL(SELECT id,num FROM b)) as tb GROUP BY id;
 
 
 
 
 

基本定义：
 
　　left join （左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。
 
　　right join （右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录。
 
　　inner join （等值连接或者叫内连接）：只返回两个表中连接字段相等的行。
 
　　full join （全外连接）：返回左右表中所有的记录和左右表中连接字段相等的记录。
 
举个例子：
 
　A表　　　　　　　　　　

　　id　  name　　

　　1　　小王

　　2　　小李

　　3　　小刘

　　B表

　　id　　A_id　　job

　　1　　2　　　　老师

　　2　　4　　　　程序员
 
内连接：（只有2张表匹配的行才能显示）
 
select a.name,b.job from A a  inner join B b on a.id=b.A_id

　　只能得到一条记录

　　小李　　老师
 
左连接：（左边的表不加限制）
 
select a.name,b.job from A a  left join B b on a.id=b.A_id

　　三条记录

　　小王　　null

　　小李　　老师

　　小刘　　null
 
右连接：（右边的表不加限制）
 
select a.name,b.job from A a  right join B b on a.id=b.A_id

　　两条记录

　　小李　　老师

　　null　　程序员
 
 
 
全外连接：(左右2张表都不加限制）
 
select a.name,b.job from A a  full join B b on a.id=b.A_id

　　四条数据

　　小王　　null

　　小李　　老师

　　小刘　　null

　　null　　程序员
 
注：在sql中l外连接包括左连接（left join ）和右连接（right join），全外连接（full join），等值连接（inner join）又叫内连接。
 
 
 
我的座右铭：不会，我可以学；落后，我可以追赶；跌倒，我可以站起来；我一定行。
 
 
 

用两个表（a_table、b_table），关联字段a_table.a_id和b_table.b_id来演示一下MySQL的内连接、外连接（ 左(外)连接、右(外)连接、全(外)连接）。
 
MySQL版本：Server version: 5.6.31 MySQL Community Server (GPL)
 
数据库表：a_table、b_table
 
主题：内连接、左连接（左外连接）、右连接（右外连接）、全连接（全外连接）
 
前提
 
建表语句：
 

 
 
CREATE TABLE `a_table` (
  `a_id` int(11) DEFAULT NULL,
  `a_name` varchar(10) DEFAULT NULL,
  `a_part` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 
CREATE TABLE `b_table` (
  `b_id` int(11) DEFAULT NULL,
  `b_name` varchar(10) DEFAULT NULL,
  `b_part` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 
 
 
表测试数据：
 
 
 
 
 
 
 
一、内连接
 关键字：inner join on
 

 语句：select * from a_table a 
 inner join b_table b
 on a.a_id = b.b_id;
 

 执行结果：
 

 
 
 
 

 
 说明：组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。
 

 
 
二、左连接（左外连接）
 
 
 

  关键字：left join on / left outer join on
 
 
 

  语句：select * from a_table a 
  left join b_table b
  on a.a_id = b.b_id;
 
 
 

  执行结果：
 
 
 
 
 
 

 
 
 

 说明：
 

 left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。
 
 
 

 左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。
 
 
 

 
 
 
 
三、右连接（右外连接）
 
 
 

  关键字：right join on / right outer join on
 
 
 

  语句：select * from a_table a 
  right outer join b_table b 
  on a.a_id = b.b_id;
 
 
 

  执行结果：
 
 
 
 
 
 

 
 
 

  说明：
 
 
 

  right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。
  
 
 
 
 

  与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。
 
 
 
 
 
 
 
 
四、全连接（全外连接）
 
 

  MySQL目前不支持此种方式，可以用其他方式替代解决。
 
 
 
五、补充，MySQL如何执行关联查询
 
 

  MySQL认为任何一个查询都是一次“关联”，并不仅仅是一个查询需要到两个表匹配才叫关联，所以在MySQL中，每一个查询，每一个片段（包括子查询，甚至基于单表查询）都可以是一次关联。
 
 
 

  当前MySQL关联执行的策略很简单：MySQL对任何关联都执行嵌套循环关联操作，即MySQL先在一个表中循环取出单条数据，然后在嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。请看下面的例子中的简单的查询：
 
 
 

  
 
 
 
 

  查询语句：select tbl1.col1, tbl2.col2 from tbl1 
  inner join tbl2 using(col3) where tbl1.col1 in (5, 6);
 
 
 

  假设MySQL按照查询中的表顺序进行关联操作，我们则可以用下面的伪代码表示MySQL将如何完成这个查询：
 
 
 

  
outer_iter = iterator over tbl1 where col1 in (5, 6)
outer_row = outer_iter.next
while outer_row
    inner_iter = iterator over tbl2 where col3 = outer_row.col3
    inner_row = inner_iter.next
    while inner_row
        output [ outer_row.col1, inner_row.col2]
        inner_row = inner_iter.next
    end
    outer_row = outer_iter.next
end
上面的执行计划对于单表查询和多表关联查询都适用，如果是一个单表查询，那么只需要上面外层的基本操作。对于外连接，上面的执行过程仍然适用。例如，我们将上面的查询语句修改如下：
  
 select tbl1.col1, tbl2.col2 from tbl1 
  left outer join tbl2 using(col3) where tbl1.col1 in (5, 6);
  
 
 
 
 

  那么，对应的伪代码如下：
 
 
 

  
outer_iter = iterator over tbl1 where col1 in (5, 6)
outer_row = outer_iter.next
while outer_row
    inner_iter = iterator over tbl2 where col3 = outer_row.col3
    inner_row = inner_iter.next
    if inner_row
        while inner_row
            output [ outer_row.col1, inner_row.col2]
            inner_row = inner_iter.next
        end
    else
        output [ outer_row.col1, null]
    end
        outer_row = outer_iter.next
end
  说明：第五部分摘自《高性能MySQL 第三版》