深入理解SQL的四种连接-左外连接、右外连接、内连接、全连接

1、内联接（典型的联接运算，使用像 = 或 <> 之类的比较运算符）。包括相等联接和自然联接。
内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students和courses表中学生标识号相同的所有行。

2、外联接。外联接可以是左向外联接、右向外联接或完整外部联接。
在 FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：
1）LEFT JOIN或LEFT OUTER JOIN
左向外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
2）RIGHT JOIN 或 RIGHT OUTER JOIN
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。
3）FULL JOIN 或 FULL OUTER JOIN
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

3、交叉联接
交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。
FROM 子句中的表或视图可通过内联接或完整外部联接按任意顺序指定；但是，用左或右向外联接指定表或视图时，表或视图的顺序很重要。有关使用左或右向外联接排列表的更多信息，请参见使用外联接。

**

1. join等价于inner join内连接，是返回两个表中都有的符合条件的行。
   在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中 的所有列，包括其中的重复列。在使用join，inner join，和不使用join关键字，直接连表的限制条件不一样，如果是使用了join关键字的sql语句，是使用on作为等值连接的限制条件，elect * from a join b on a.id=b.id如果不使用join，如 select * from a,b where a.id=b.id。
2. left join左连接，是返回左表中所有的行及右表中符合条件的行。
3. right join右连接，是返回右表中所有的行及左表中符合条件的行。
4. full join全连接，是返回左表中所有的行及右表中所有的行，并按条件连接。
   通常情况下，left join肯定比inner join返回的行数多。**

1、左外连接— left join

2、右外连接 — right join

3、全外连接

4、内连接 —inner join

**例子 **
1） 内连接
select a.,b. from a inner join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2

2）左连接
select a.,b. from a left join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
3 王武 null

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

4） 完全连接
select a.,b. from a full join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
null 　　 3 34 4
3 王武 null

其他博客总结：
select过程：from->where->group by->having->order by->limit
在不使用on语法时，join、inner join、逗号、cross join结果相同，都是取2个表的笛卡尔积。逗号与其他操作符优先级不同，所以有可能产生语法错误，尽量减少用逗号

join、inner join、cross join支持on和using语法，逗号不支持on和using语法
on语法：筛选连接后的结果，两表的对应列值相同才在结果集中，可以通过and连接多个列值的匹配要求，列名可以不同
select * from tb_test1 inner join tb_student on tb_test1.id=tb_student.id;
using语法：筛选连接后的结果，两表的对应列值相同才在结果集中，括号内用多个列名要求用逗号连接，列名必须相同
select * from tb_test1 cross join tb_student using(id);
仅MySQL中！：（MySQL中没有全外连接，用UNION操作实现吧）
join=inner join=cross join（笛卡尔积）
left join=left outer join
right join=right outer join

sample数据：

select * from tb_test1;
±—±----------±-------±------+
| id | name | gender | score |
±—±----------±-------±------+
| 1 | 李毅 | 男 | 95.30 |
| 2 | 二丫 | 女 | 95.30 |
| 3 | 张三 | 女 | 95.30 |
| 4 | 李四 | 女 | 95.30 |
| 7 | 胡鲁瓦 | 男 | 95.30 |
| 9 | 后羿 | 男 | 95.30 |
±—±----------±-------±------+

select * from tb_student;
±—±-------±-------±-----------+
| id | name | gender | birthday |
±—±-------±-------±-----------+
| 1 | 李毅 | 男 | 1988-03-20 |
| 2 | kevin | 男 | 1987-08-23 |
| 3 | marry | 女 | 1989-11-25 |
| 4 | lucy | 女 | 1989-11-25 |
| 5 | lily | 女 | 1992-01-25 |
±—±-------±-------±-----------+

natural join：自然连接（不允许带on/using）
natrual join：全自然连接，对左右2个表相同名字的列进行等值匹配，不可用on，using操作指定，自动删除多余重名列
natrual left join：左自然连接，保留2个表的列（删除多余重名列），以左表为准，不存在匹配的右表列，值置为NULL
natrual right join：和左自然连接相反

select * from tb_test1 natural join tb_student;
±—±-------±-------±------±-----------+
| id | name | gender | score | birthday |
±—±-------±-------±------±-----------+
| 1 | 李毅 | 男 | 95.30 | 1988-03-20 |
±—±-------±-------±------±-----------+

select * from tb_test1 natural left join tb_student;
±—±----------±-------±------±-----------+
| id | name | gender | score | birthday |
±—±----------±-------±------±-----------+
| 1 | 李毅 | 男 | 95.30 | 1988-03-20 |
| 2 | 二丫 | 女 | 95.30 | NULL |
| 3 | 张三 | 女 | 95.30 | NULL |
| 4 | 李四 | 女 | 95.30 | NULL |
| 7 | 胡鲁瓦 | 男 | 95.30 | NULL |
| 9 | 后羿 | 男 | 95.30 | NULL |
±—±----------±-------±------±-----------+

select * from tb_test1 natural right join tb_student;
±—±-------±-------±-----------±------+
| id | name | gender | birthday | score |
±—±-------±-------±-----------±------+
| 1 | 李毅 | 男 | 1988-03-20 | 95.30 |
| 2 | kevin | 男 | 1987-08-23 | NULL |
| 3 | marry | 女 | 1989-11-25 | NULL |
| 4 | lucy | 女 | 1989-11-25 | NULL |
| 5 | lily | 女 | 1992-01-25 | NULL |
±—±-------±-------±-----------±------+

inner join：内连接（不带on/using时做笛卡尔积）
主表和关联表的笛卡尔积结果，通过on或者using操作筛选结果集（先求笛卡尔积，后筛选，所以性能上和where一样），2个表的所有列都被保存

select * from tb_student a join tb_test1 b on a.id=b.id;
±—±-------±-------±-----------±—±-------±-------±------+
| id | name | gender | birthday | id | name | gender | score |
±—±-------±-------±-----------±—±-------±-------±------+
| 1 | 李毅 | 男 | 1988-03-20 | 1 | 李毅 | 男 | 95.30 |
| 2 | kevin | 男 | 1987-08-23 | 2 | 二丫 | 女 | 95.30 |
| 3 | marry | 女 | 1989-11-25 | 3 | 张三 | 女 | 95.30 |
| 4 | lucy | 女 | 1989-11-25 | 4 | 李四 | 女 | 95.30 |
±—±-------±-------±-----------±—±-------±-------±------+

select * from tb_student a join tb_test1 b on a.gender=b.gender and a.name=b.name;
±—±-------±-------±-----------±—±-------±-------±------+
| id | name | gender | birthday | id | name | gender | score |
±—±-------±-------±-----------±—±-------±-------±------+
| 1 | 李毅 | 男 | 1988-03-20 | 1 | 李毅 | 男 | 95.30 |
| 6 | 李毅 | 男 | 1985-10-11 | 1 | 李毅 | 男 | 95.30 |
±—±-------±-------±-----------±—±-------±-------±------+

left join：左外连接（必须带on/using，等价与left outer join）
左表为主表，保留全部记录全部列，右表为关联表，加上on操作之外的其他列，符合on条件的记录连接上

on和where的区别（在外连接时有差别）：on和where在性能上不存在差异，都是在笛卡尔积后筛选，但on在from阶段，属于join操作，先于where。所以当left join连接时，虽然左表的列不符合on条件，也必须保留，只是该记录右表全是null。而where对连接后的结果做筛选，不符合条件的直接剔除

select * from tb_student a left join tb_test1 b on a.id=b.id;
±—±-------±-------±-----------±-----±-------±-------±------+
| id | name | gender | birthday | id | name | gender | score |
±—±-------±-------±-----------±-----±-------±-------±------+
| 1 | 李毅 | 男 | 1988-03-20 | 1 | 李毅 | 男 | 95.30 |
| 2 | kevin | 男 | 1987-08-23 | 2 | 二丫 | 女 | 95.30 |
| 3 | marry | 女 | 1989-11-25 | 3 | 张三 | 女 | 95.30 |
| 4 | lucy | 女 | 1989-11-25 | 4 | 李四 | 女 | 95.30 |
| 5 | lily | 女 | 1992-01-25 | NULL | NULL | NULL | NULL |
| 6 | 李毅 | 男 | 1985-10-11 | NULL | NULL | NULL | NULL |
±—±-------±-------±-----------±-----±-------±-------±------+

select * from tb_student a left join tb_test1 b on a.id=b.id and a.id<4;
±—±-------±-------±-----------±-----±-------±-------±------+
| id | name | gender | birthday | id | name | gender | score |
±—±-------±-------±-----------±-----±-------±-------±------+
| 1 | 李毅 | 男 | 1988-03-20 | 1 | 李毅 | 男 | 95.30 |
| 2 | kevin | 男 | 1987-08-23 | 2 | 二丫 | 女 | 95.30 |
| 3 | marry | 女 | 1989-11-25 | 3 | 张三 | 女 | 95.30 |
| 4 | lucy | 女 | 1989-11-25 | NULL | NULL | NULL | NULL |
| 5 | lily | 女 | 1992-01-25 | NULL | NULL | NULL | NULL |
| 6 | 李毅 | 男 | 1985-10-11 | NULL | NULL | NULL | NULL |
±—±-------±-------±-----------±-----±-------±-------±------+

select * from tb_student a left join tb_test1 b on a.id=b.id where a.id<4;
±—±-------±-------±-----------±-----±-------±-------±------+
| id | name | gender | birthday | id | name | gender | score |
±—±-------±-------±-----------±-----±-------±-------±------+
| 1 | 李毅 | 男 | 1988-03-20 | 1 | 李毅 | 男 | 95.30 |
| 2 | kevin | 男 | 1987-08-23 | 2 | 二丫 | 女 | 95.30 |
| 3 | marry | 女 | 1989-11-25 | 3 | 张三 | 女 | 95.30 |
±—±-------±-------±-----------±-----±-------±-------±------+

right join：右外连接，和左外连接相反