SQL-连接查询

1. 连接查询：

    1) 即查询的时候同时需要多张表（特别是存在外键关系的），此时需要多张表之间的值进行连接；

    2) 目前SQL标准提出过两种连接查询，第一种是较早的SQL92标准，第二种是目前使用广泛的较新的SQL99标准；

    3) 92形式简单，但编写较为冗长，99不仅在底层得到优化，而且形式看上去更加一目了然，逻辑性更强，一般建议使用99标准；

2. SQL92：

    1) 多张表需要全部放在from之后，所有的连接条件都放在where当中，因此SQL92中的等值连接、非等值链接、外连接等等其实只是where中的条件不同罢了；

    2) 具体语法：

[sql]  view plain  copy

1. select col1, col2, ...  
2. from table1 t1, table2 t2, ...  
3. where ...  

！！可以看到需要为表取别名（可以使用as），t1、t2就是各个表的别名，这样就可以在col1、col2、...以及where子句中使用引用这些别名了；

！！区别名的目的：一方面是为了简化表名（原表名可能很长），其次可能发生自连接的情况（即自己跟自己连接），因此需要为同一张表取两个不一样的别名，这样连接的时候可以“当做”两张不同的表使用；

    3) 示例：

[sql]  view plain  copy

1. select s.col1, t.col2  
2. from table1 s, table2 t  
3.   
4. 1. where s.col1 = t.id; // 值相等就是等值连接  
5. 2. where s.col1 > t.id; // 不是相等就是非等值连接  
6. 3. ; // 无where子句就是广义笛卡尔积  

    4) 连接的原理：按照from后面表的出现顺序，前面的表作为内存的for循环，后出现的表作为外层的for循环，上面的例子就是这样实现的

[sql]  view plain  copy

1. for t in table2  
2. {  
3.     for s in talbe1  
4.     {  
5.         if 满足where条件 // 广义笛卡尔积就是去掉if语句直接output  
6.             output s + t;  
7.     }  
8. }  

！！可以看到where子句中以及select后的"表名（或者别名）+'.'+列名"的用法，在这里表名（别名）其实就代表当前查出来的一条记录了，而后面跟着的列名就代表该记录的哪个字段；

    5) 自连接：就是一张表自己跟自己连接，比如员工和经理都在一张表中，现在要将员工的所属经理的ID和经理的ID连接，此时就要将该表当成两种表看，一张是普通的员工表，另一张是经理表（只不过经理标中加载这员工，员工表中夹杂着经理，通常需要自连接都表示数据库设计不合理，非常不提倡）；

[sql]  view plain  copy

1. select ...  
2. from table s, table t  
3. where ...  

！！可以看到只要为同一张表命不同的别名加以区分就行了，可以带到上面的嵌套for循环中检验一下；

    6) 外连接：各数据库对SQL92的外连接支持的并不是很好，基本都是在SQL99中全面支持外连接的，这里MySQL就不支持SQL92的外连接；

！！以下内容全部都属于SQL99：

3. SQL99、交叉连接（SQL92的广义笛卡尔积）：

    1) 基本覆盖了全部类型的连接查询（包括外连接），并且各大数据库都能全部支持SQL99的连接查询；

    2) 直接使用关键字：cross join（交叉连接，即广义笛卡尔积）、natrual join（自然连接）、left join/right join（左右外连接）表示连接类型；

    3) 使用"table xxx join table"的语法来表示哪张表和哪张表进行什么样的连接，连接类型一目了然，写法更加符合逻辑，可读性更好，并且底层支持地更好；

    4) 交叉连接：即SQL92的广义笛卡尔积，语法如下

[sql]  view plain  copy

1. select ...  
2. from 表1  
3. cross join 表2  
4. [corss join 表3...]  

！如果有很多表要交叉连接，那么就继续cross join下去；

    5) 如果select选出的列刚好在各个表中有重名，则需要为表取别名以示区别；

    6) 示例：

[sql]  view plain  copy

1. select s.col1, t.col2  
2. from t1 as s  
3. cross join t2 as t;  

！！一般区别名还是建议使用as，如果一下用太多空格可读性会大大降低！并且as见名知意；


4. 自然连接——通名列组合等值连接：

    1) 语法跟交叉连接一模一样，关键字是natural join，格式如下：

[sql]  view plain  copy

1. select ...  
2. from 表1  
3. natural join 表2  
4. [natural join 表3...]  

    2) 自然连接看上去就是交叉连接，但不过自然连接是有条件，它是等值连接的一种，默认连接的两张表同名列的组合相等，比如t1和t2进行自然连接，t1有a, b, c三列，t2有b, c, d三列，那么自然连接时就是找组合t1.(b, c)=t2.(b, c)的记录连接！记住是同列的组合！！

5. 指定同名列等值连接：

    1) 前面的自然连接默认会将所有通名列的组合做等值连接，但SQL99也提供了join using语法来指定那些通名列可以做等值连接；

    2) 如果多表之间有通名列，则可以人为指定用哪些通名列做等值连接而不是全部通名列的组合；

    3) 格式：

[sql]  view plain  copy

1. select ...  
2. from 表1   
3.     join 表2 using(指定通名列或组合)  
4.         join 表3 using(指定通名列或组合)  
5.             ...  

    4) 例如：

[sql]  view plain  copy

1. select *  
2. from t1  
3.     join t2 using(col1, col2);  

！表示t1和t2以同名列col1、col2的组合做等值连接；

    5) SQL99专门为通名列等值连接准备了两个语法（natural join和join using），可以通名列等值连接在实际中还是非常常用的！

6. 自定义等值/不等值连接——对SQL92的扩展：

    1) 上面介绍的等值连接都是基于通名列的，那如果想要作为连接条件的列不同名怎么办呢？

    2) SQL99提供了join on来达到上述目的，其语法格式：

[sql]  view plain  copy

1. select ...  
2. from 表1 别名1  
3.     join 表2 别名2 on 表1表2的连接条件  
4.         join 表3 别名3 on 表2表3的连接条件  
5.             ...  

    3) 如果连接条件是相等判断那就是等值连接，如果是大于、小于等之类的判断那就是不等值连接了；

    4) 例如：等值连接

[sql]  view plain  copy

1. select *  
2. from t1  
3.     join t2 on t1.col1 = t2.col2;  

    5) 例如：不等值连接

[sql]  view plain  copy

1. select *  
2. from t1  
3.     join t2 on t1.col1 > t2.col2;  

    6) 可以看到这和SQL92的语法非常相似，只不过92的条件是where子句确定的，而99的条件是由on确定的，但是92显得更加简单，为什么要继续开发出99呢？

7. 多表连接的逻辑——为什么要用SQL99：

    1) 假设3表连接，用92：

[sql]  view plain  copy

1. select *  
2. from t1, t2, t3  
3. where t1.col = t3.col and t2.col = t3.col;  

！底层的for循环其实是由嵌套顺序，而该SQL语句非常混乱，首先是嵌套顺序各个数据库实现可能不一样，MySQL是t1（外）->t2（中）->t3（内），但其它数据库的嵌套顺序可能相反，但如果你明白的知道嵌套顺序等细节就可以在写SQL语句的时候对查询进行优化；

    2) 而SQL99不存在这个问题，它的join关键字其实就是一个显示的for循环！！例如：

[sql]  view plain  copy

1. select *  
2. from t1  
3.     join t2 on condition1  
4.         join t3 on condition2  

！就是非常明确的：

[sql]  view plain  copy

1. for t1 in table1  
2. {  
3.     for t2 in table2  
4.     {  
5.         if condition1  
6.         {  
7.             for t3 in table3  
8.             {  
9.                 if condition2  
10.                     output t1 + t2 + t3;  
11.             }  
12.         }  
13.     }  
14. }  

    3) 因此在使用SQL99进行多表连接时，连接的顺序本身就是底层for循环的顺序，嵌套结构非常清晰，一目了然，就跟直接写for嵌套一样，因此99的多表连接显而易见有如下几个性质：

         i. 连接的对象只能是两张表，即两两连接，传递起来就看上去像多张表同时连接，其实就是最近的两层for循环连接在了一起；

         ii. 由for循环的作用于可知外层循环不能访问内存循环的变量（内层对外层不可见），因此SQL99多表连接中上层表的condition中不能访问下层表，比如在上面的例子中condition1里不能出现t3，但是下层表可以访问上层表（for循环嵌套中外层的变量对内层来说是可见的），即condition2中可以出现t1和t2！

8. 再看自然连接和using连接在超过2张表时的情形：

    1) 先看自然连接：

[sql]  view plain  copy

1. select *  
2. from t1  
3.     natural join t2  
4.         natural join t3  

！的言下之意就是：

[sql]  view plain  copy

1. select *  
2. from t1  
3.     join t2 using(t1-t2之间的同名列组)  
4.         join t3 using(t2-t3之间的同名列组)  

！即条件是相邻两列之间的，而并非全部的！！

    2) using同样也符合这个道理，例如：

[sql]  view plain  copy

1. select *  
2. from t1  
3.     join t2 using(col1)  
4.         join t3 using(col1)  

！言下之意是：

[sql]  view plain  copy

1. select *  
2. from t1  
3.     join t2 using(t1-t2之间的同名列col1)  
4.         join t3 using(t2-t3之间的同名列col1)  

！！SQL92的多表连接查询的连接条件永远是相邻两列之间的！！！

9. 外连接：

    1) 相比于外连接，之前的连接全部都算内连接，内连接就是只要符合condition条件的连接都输出，相反SQL的外连接不仅要把符合condition条件的连接输出，也要把不符合condition的连接也输出，但不是完整的输出！！！

    2) 什么意思呢？先来看一下左外连接：

         i. table1 左外连接 table2 condition  的言下之意是

[cpp]  view plain  copy

1. for t1 in table1  
2. {  
3.     for t2 in table2  
4.     {  
5.         if t1-t2满足condition  
6.             output t1 + t2;  
7.     }  
8.   
9.     if table2中任意一个t2都不能让<span style="font-family: Arial, Helvetica, sans-serif;">t1-t2满足condition</span>  
10.         output t1 + null  
11. }  

！即满足条件的连接还是正常地一条一条全部打印出来，但是如果左表（t1）的记录找不到右表（t2）任意一条记录能和它满足条件，那么就要输出一条结果，就是不满足条件的那个坐标记录，但右表的部分全部为null；

         ii. 而右外连接正好相反（嵌套顺序也相反）：table1 右外连接 table2 condition  的言下之意是

[cpp]  view plain  copy

1. for t2 in table2  
2. {  
3.     for t1 in table1  
4.     {  
5.         if t1-t2满足condition  
6.             output t1 + t2;  
7.     }  
8.   
9.     if table1中任意一个t1都不能让t1-t2满足condition  
10.         output null + t2;  
11. }  

         iii. 全外连接就是左外和右外的结合：table1 全外连接 table2 condition  的言下之意是

[cpp]  view plain  copy

1. for t1 in table1  // 满足条件的全部输出  
2. {  
3.     for t2 in table2  
4.     {  
5.         if t1-t2满足condition  
6.             output t1 + t2;  
7.     }  
8. }  
9.   
10. for t1 in table1  // 输出左外连接  
11. {  
12.     if table2中任意一个t2都不能让t1-t2满足condition  
13.         output t1 + null;  
14. }  
15.   
16. for t2 in table2  // 输出右外连接  
17. {  
18.     if table1中的任意一个t1都不能让t1-t2满足condition  
19.         output null + t2;  
20. }  

    3) SQL99的外连接语法：

         i. 关键字是left join（左外连接）、right join（右外连接）、full join（全外连接）；

         ii. 格式是：

[sql]  view plain  copy

1. select ...  
2. from 表1  
3.     left/right/full join 表2 on 条件1  
4.         left/right/full join 表3 on 条件2  
5.             ....