探究 MYSQL join的用法

1、先看下执行顺序：


2、再看下各种说法：

（1）FROM 阶段

    FROM阶段标识出查询的来源表，并处理表运算符。在涉及到联接运算的查询中（各种join），主要有以下几个步骤：

　　a.求笛卡尔积。不论是什么类型的联接运算，首先都是执行交叉连接（cross join），求笛卡儿积，生成虚拟表VT1-J1。

      b.ON筛选器。这个阶段对上个步骤生成的VT1-J1进行筛选，根据ON子句中出现的谓词进行筛选，让谓词取值为true的行通过了考验，插入到VT1-J2。

      c.添加外部行。如果指定了outer join，还需要将VT1-J2中没有找到匹配的行，作为外部行添加到VT1-J2中，生成VT1-J3。

    经过以上步骤，FROM阶段就完成了。概括地讲，FROM阶段就是进行预处理的，根据提供的运算符对语句中提到的各个表进行处理（除了join，还有apply，pivot，unpivot）

（2）WHERE阶段

     WHERE阶段是根据<where_predicate>中条件对VT1中的行进行筛选，让条件成立的行才会插入到VT2中。

select a.*,
b.result 
from t1 a  
left join t2 b on a.uId=b.uId and a.class='1'
-------t1表全显示

select a.*,b.result from t1 a  
left join t2 b on a.uId=b.uId where a.class='1'

t1表显示Class=1的全部数据

要限制T1表的结果集，可以在where后果加

在 left join后面加，只是根据条件显示T2是否null

如果改为 inner join 时，结果集相同

LEFT JOIN 或 LEFT OUTER JOIN。 
左向外部联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某一行在右表中没有匹配行，则在关联的结果集行中，来自右表的所有选择列表列均为空值。 

RIGHT JOIN 或 RIGHT OUTER JOIN 
右向外部联接是左向外部联接的反向联接。将返回右表的所有行。如果右表的某一行在左表中没有匹配行，则将为左表返回空值。 

FULL JOIN 或 FULL OUTER JOIN 
完整外部联接将返回左表和右表中的所有行。当某一行在另一个表中没有匹配行时，另一个表的选择列表列将包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。 

直接是join的话ON后的条件写到where后边是没错的。

如果是 left join   right join 的话，楼主要多注意些：

看看你left join后的表是先筛选还是和主表连接后再进行筛选，一个逻辑的先后顺序问题。

你可以去看看SQL查询的执行顺序就了解了。

inner join 中的on条件
和where条件是一样的
left join 和right join 就不一样了

ON... AND...这样是把AND连接的两个条件都作为了过滤条件

ON...WHERE 这样是先用ON后面的条件过滤 再与WHERE 后的匹配 执行顺序都不一样了

如果你查一下SQL2000的联机丛书,它里面就有这样一段话:
在 ON 子句中可能有仅涉及一个联接表的谓词。这样的谓词也可能出现在查询中的 WHERE 子句中。尽管这种谓词的放置在 INNER 联接中不会产生差别，但是在涉及 OUTER 联接时可能会导致不同的结果。这是因为 ON 子句中的谓词在应用于联接之前先应用于表，而 WHERE 子句在语意上应用于联接结果。

我记得2000的联机丛书还有这样一句话,意思是说不推荐使用 where 作为联结的查询方式,因为在有时候它可能会产生二义性

如何可以，请不要在join中加入非连接条件 如 on x=x and y=1这样的语句，
Join时有个隐含的算法join 这三个分别是loop, hash 和merge 在你研究了这三个算法对join的影响以后，在对索引连接表时的性能有一定的把握以后，你就能更加明白where中放入限制语句的和join中放入限制语句的性能上的巨大差异。

http://www.cnblogs.com/gaiyang/archive/2011/04/13/2014355.html

http://www.cnblogs.com/gaiyang/archive/2011/04/13/2014356.html


三、试验

1、表数据：

mysql> select * from vehicle as a;
+------+-----------+--------+---------------------+
| id | hphm | hpzl | jgsj |
+------+-----------+--------+---------------------+
| 1 | 冀X11111 | 轿车 | 2015-08-24 14:06:54 |
| 2 | 冀X11111 | 轿车 | 2015-08-24 14:07:12 |
| 3 | 冀X22222 | 轿车 | 2015-08-24 14:07:36 |
| 4 | 冀X22222 | 轿车 | 2015-08-24 13:07:49 |
| 5 | 冀X33333 | 客车 | 2015-08-24 14:08:12 |
| 6 | 冀X33333 | 客车 | 2015-08-24 14:08:51 |
| 7 | 冀X44444 | 轿车 | 2015-08-31 15:54:10 |
+------+-----------+--------+---------------------+
7 rows in set (0.00 sec)

mysql> select * from xinxi as b;
+------+------+-----------+
| id | clnk | hphm |
+------+------+-----------+
| 1 | 2010 | 冀X11111 |
| 2 | 2012 | 冀X22222 |
| 3 | 2013 | 冀X33333 |
| 4 | 2015 | 冀X55555 |
+------+------+-----------+
4 rows in set (0.00 sec)

2、条件放入 on 和 where 的区别
inner join: 无区别

mysql> select * from vehicle a inner join  xinxi b on a.hphm=b.hphm where a.hphm='冀X11111';
+------+-----------+--------+---------------------+------+------+-----------+
| id | hphm | hpzl | jgsj | id | clnk | hphm |
+------+-----------+--------+---------------------+------+------+-----------+
| 1 | 冀X11111 | 轿车 | 2015-08-24 14:06:54 | 1 | 2010 | 冀X11111 |
| 2 | 冀X11111 | 轿车 | 2015-08-24 14:07:12 | 1 | 2010 | 冀X11111 |
+------+-----------+--------+---------------------+------+------+-----------+
2 rows in set (0.03 sec)

mysql> select * from vehicle a inner join  xinxi b on a.hphm=b.hphm and a.hphm='冀X11111';
+------+-----------+--------+---------------------+------+------+-----------+
| id | hphm | hpzl | jgsj | id | clnk | hphm |
+------+-----------+--------+---------------------+------+------+-----------+
| 1 | 冀X11111 | 轿车 | 2015-08-24 14:06:54 | 1 | 2010 | 冀X11111 |
| 2 | 冀X11111 | 轿车 | 2015-08-24 14:07:12 | 1 | 2010 | 冀X11111 |
+------+-----------+--------+---------------------+------+------+-----------+
2 rows in set (0.00 sec)

left join:  hphm='冀X11111' 放到 on 里面后，返回了 a 表的所有结果；放到where 后，返回交集。

mysql> select * from vehicle a left join  xinxi b on a.hphm=b.hphm where a.hphm='冀X11111';
+------+-----------+--------+---------------------+------+------+-----------+
| id | hphm | hpzl | jgsj | id | clnk | hphm |
+------+-----------+--------+---------------------+------+------+-----------+
| 1 | 冀X11111 | 轿车 | 2015-08-24 14:06:54 | 1 | 2010 | 冀X11111 |
| 2 | 冀X11111 | 轿车 | 2015-08-24 14:07:12 | 1 | 2010 | 冀X11111 |
+------+-----------+--------+---------------------+------+------+-----------+
2 rows in set (0.00 sec)

mysql> select * from vehicle a left join  xinxi b on a.hphm=b.hphm and a.hphm='冀X11111';
+------+-----------+--------+---------------------+------+------+-----------+
| id | hphm | hpzl | jgsj | id | clnk | hphm |
+------+-----------+--------+---------------------+------+------+-----------+
| 1 | 冀X11111 | 轿车 | 2015-08-24 14:06:54 | 1 | 2010 | 冀X11111 |
| 2 | 冀X11111 | 轿车 | 2015-08-24 14:07:12 | 1 | 2010 | 冀X11111 |
| 3 | 冀X22222 | 轿车 | 2015-08-24 14:07:36 | NULL | NULL | NULL |
| 4 | 冀X22222 | 轿车 | 2015-08-24 13:07:49 | NULL | NULL | NULL |
| 5 | 冀X33333 | 客车 | 2015-08-24 14:08:12 | NULL | NULL | NULL |
| 6 | 冀X33333 | 客车 | 2015-08-24 14:08:51 | NULL | NULL | NULL |
| 7 | 冀X44444 | 轿车 | 2015-08-31 15:54:10 | NULL | NULL | NULL |
+------+-----------+--------+---------------------+------+------+-----------+
7 rows in set (0.00 sec)

3、inno join 和 left Join 结果对比
对于a 存在，b也存在的记录，join 和 left join 返回结果一致。

对于a 存在，b不存在的记录： join 返回空，left join 返回a对应的记录

mysql> select * from vehicle a  join  xinxi b on a.hphm=b.hphm where a.hphm='冀X44444';
Empty set (0.00 sec)
mysql> select * from vehicle a left join  xinxi b on a.hphm=b.hphm where a.hphm='冀X44444';
+------+-----------+--------+---------------------+------+------+------+
| id   | hphm      | hpzl   | jgsj                | id   | clnk | hphm |
+------+-----------+--------+---------------------+------+------+------+
|    7 | 冀X44444  | 轿车   | 2015-08-31 15:54:10 | NULL | NULL | NULL |
+------+-----------+--------+---------------------+------+------+------+
1 row in set (0.00 sec)

对于a 不存在，b存在的记录： 都返回为空

mysql> select * from vehicle a left join  xinxi b on a.hphm=b.hphm where a.hphm='冀X55555';
Empty set (0.01 sec)

mysql> select * from vehicle a left join  xinxi b on a.hphm=b.hphm where a.hphm='冀X55555';
Empty set (0.01 sec)

mysql> select * from vehicle a left join  xinxi b on a.hphm=b.hphm where b.hphm='冀X55555';
Empty set (0.00 sec)

mysql> select * from vehicle a  join  xinxi b on a.hphm=b.hphm where a.hphm='冀X55555';
Empty set (0.00 sec)

mysql> select * from vehicle a  join  xinxi b on a.hphm=b.hphm where b.hphm='冀X55555';
Empty set (0.00 sec)

四、总结
1、关于 条件是放到 on 还是 where 的问题：
join 的条件放到 on 和 放到 where  下是一致的
left join 的 on 中加入非连接条件 如 on x=x and y=1 ，结果是不可控的
2、