初识sql语法之join
2017-01-16 14:41:12 
阿炯
SQL中的连接可以分为内连接，外连接，以及交叉连接 。现有两张表，Table A 是左边的表。Table B 是右边的表。其各有四条记录，其中有两条记录name是相同的：
1. 交叉连接CROSS JOIN
如果不带WHERE条件子句，它将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积。
一般不建议使用方法A和B，因为如果有WHERE子句的话，往往会先生成两个表行数乘积的行的数据表然后才根据WHERE条件从中选择。因此，如果两个需要求交际的表太大，将会非常非常慢，不建议使用。
注：cross join后加条件只能用where，不能用on。
2. 内连接INNER JOIN
两边表同时符合条件的组合
如果仅仅使用
SELECT * FROM tablea INNER JOIN tableb
内连接如果没有指定连接条件的话，和笛卡尔积的交叉连接结果一样，但是不同于笛卡尔积的地方是，没有笛卡尔积那么复杂要先生成行数乘积的数据表，内连接的效率要高于笛卡尔积的交叉连接。
但是通常情况下，使用INNER JOIN需要指定连接条件。
关于等值连接和自然连接
等值连接(=号应用于连接条件, 不会去除重复的列)
自然连接(会去除重复的列)
数据库的连接运算都是自然连接，因为不允许有重复的行(元组)存在。
3. 外连接OUTER JOIN
指定条件的内连接，仅仅返回符合连接条件的条目。
外连接则不同，返回的结果不仅包含符合连接条件的行，而且包括左表(左外连接时), 右表(右连接时)或者两边连接(全外连接时)的所有数据行。
1)左外连接LEFT [OUTER] JOIN
显示符合条件的数据行，同时显示左边数据表不符合条件的数据行，右边没有对应的条目显示NULL。
2)右外连接RIGHT [OUTER] JOIN
显示符合条件的数据行，同时显示右边数据表不符合条件的数据行，左边没有对应的条目显示NULL。
3)全外连接FULL [OUTER] JOIN
显示符合条件的数据行，同时显示左右不符合条件的数据行，相应的左右两边显示NULL，即显示左连接、右连接和内连接的并集。
缺省情况下是inner join，开发中使用的left join和right join属于outer join，另外outer join还包括full join。
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录。left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的。换言之，左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(A.aID = B.bID).B表记录不在的地方均为NULL。
LEFT JOIN操作用于在任何的 FROM 子句中，组合来源表的记录。使用 LEFT JOIN 运算来创建一个左边外部联接，左边外部联接将包含了从第一个(左边)开始的两个表中的全部记录，即使在第二个(右边)表中并没有相符值的记录。
语法：FROM table1 LEFT JOIN table2 ON table1.field1 comp table2.field2
说明：table1, table2参数用于指定要将记录组合的表的名称。
field1, field2参数指定被联接的字段的名称。且这些字段必须有相同的数据类型及包含相同类型的数据，但它们不需要有相同的名称。
comp参数指定关系比较运算符："="，"<"，">"，"<="，">=" 或 "<>"。
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录，这和left join的结果刚好相反，这次是以右表(B)为基础的，A表不在的地方用NULL填充。
inner join(等值连接) 只返回两个表中联结字段相等的行，即只显示出了A.aID = B.bID的记录，这说明inner join并不以谁为基础，它只显示符合条件的记录。
SQL中on条件与where条件的区别及having
数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。
在使用left jion时，on和where条件的区别如下：
1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。
不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。 而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。
on、where、having的区别
on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后。有时候如果这先后顺序不影响中间结果的话，那最终结果是相同的。但因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的。
根据上面的分析，可以知道where也应该比having快点的，因为它过滤数据后才进行聚合，所以having是最慢的。
在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能，在速度上后者要慢。如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据上面写的工作流程，where的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。
在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里
参考来源
SQL中inner join、outer join和cross join的区别
1. 交叉连接CROSS JOIN
如果不带WHERE条件子句，它将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积。
一般不建议使用方法A和B，因为如果有WHERE子句的话，往往会先生成两个表行数乘积的行的数据表然后才根据WHERE条件从中选择。因此，如果两个需要求交际的表太大，将会非常非常慢，不建议使用。
注：cross join后加条件只能用where，不能用on。
2. 内连接INNER JOIN
两边表同时符合条件的组合
如果仅仅使用
SELECT * FROM tablea INNER JOIN tableb
内连接如果没有指定连接条件的话，和笛卡尔积的交叉连接结果一样，但是不同于笛卡尔积的地方是，没有笛卡尔积那么复杂要先生成行数乘积的数据表，内连接的效率要高于笛卡尔积的交叉连接。
但是通常情况下，使用INNER JOIN需要指定连接条件。
关于等值连接和自然连接
等值连接(=号应用于连接条件, 不会去除重复的列)
自然连接(会去除重复的列)
数据库的连接运算都是自然连接，因为不允许有重复的行(元组)存在。
3. 外连接OUTER JOIN
指定条件的内连接，仅仅返回符合连接条件的条目。
外连接则不同，返回的结果不仅包含符合连接条件的行，而且包括左表(左外连接时), 右表(右连接时)或者两边连接(全外连接时)的所有数据行。
1)左外连接LEFT [OUTER] JOIN
显示符合条件的数据行，同时显示左边数据表不符合条件的数据行，右边没有对应的条目显示NULL。
2)右外连接RIGHT [OUTER] JOIN
显示符合条件的数据行，同时显示右边数据表不符合条件的数据行，左边没有对应的条目显示NULL。
3)全外连接FULL [OUTER] JOIN
显示符合条件的数据行，同时显示左右不符合条件的数据行，相应的左右两边显示NULL，即显示左连接、右连接和内连接的并集。
缺省情况下是inner join，开发中使用的left join和right join属于outer join，另外outer join还包括full join。
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录。left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的。换言之，左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(A.aID = B.bID).B表记录不在的地方均为NULL。
LEFT JOIN操作用于在任何的 FROM 子句中，组合来源表的记录。使用 LEFT JOIN 运算来创建一个左边外部联接，左边外部联接将包含了从第一个(左边)开始的两个表中的全部记录，即使在第二个(右边)表中并没有相符值的记录。
语法：FROM table1 LEFT JOIN table2 ON table1.field1 comp table2.field2
说明：table1, table2参数用于指定要将记录组合的表的名称。
field1, field2参数指定被联接的字段的名称。且这些字段必须有相同的数据类型及包含相同类型的数据，但它们不需要有相同的名称。
comp参数指定关系比较运算符："="，"<"，">"，"<="，">=" 或 "<>"。
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录，这和left join的结果刚好相反，这次是以右表(B)为基础的，A表不在的地方用NULL填充。
inner join(等值连接) 只返回两个表中联结字段相等的行，即只显示出了A.aID = B.bID的记录，这说明inner join并不以谁为基础，它只显示符合条件的记录。
SQL中on条件与where条件的区别及having
数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。
在使用left jion时，on和where条件的区别如下：
1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。
不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。 而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。
on、where、having的区别
on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后。有时候如果这先后顺序不影响中间结果的话，那最终结果是相同的。但因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的。
根据上面的分析，可以知道where也应该比having快点的，因为它过滤数据后才进行聚合，所以having是最慢的。
在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能，在速度上后者要慢。如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据上面写的工作流程，where的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。
在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里
参考来源
SQL中inner join、outer join和cross join的区别