Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1604644
  • 博文数量: 262
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 1894
  • 用 户 组: 普通用户
  • 注册时间: 2014-03-31 14:30
个人简介

自己慢慢积累。

文章分类

全部博文(262)

分类: Mysql/postgreSQL

2015-08-26 15:23:27

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、表数据:

点击(此处)折叠或打开

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

  14. mysql> select * from xinxi as b;
  15. +------+------+-----------+
  16. | id | clnk | hphm |
  17. +------+------+-----------+
  18. | 1 | 2010 | 冀X11111 |
  19. | 2 | 2012 | 冀X22222 |
  20. | 3 | 2013 | 冀X33333 |
  21. | 4 | 2015 | 冀X55555 |
  22. +------+------+-----------+
  23. 4 rows in set (0.00 sec)

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

点击(此处)折叠或打开

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

  9. mysql> select * from vehicle a inner join xinxi b on a.hphm=b.hphm and a.hphm='冀X11111';
  10. +------+-----------+--------+---------------------+------+------+-----------+
  11. | id | hphm | hpzl | jgsj | id | clnk | hphm |
  12. +------+-----------+--------+---------------------+------+------+-----------+
  13. | 1 | 冀X11111 | 轿车 | 2015-08-24 14:06:54 | 1 | 2010 | 冀X11111 |
  14. | 2 | 冀X11111 | 轿车 | 2015-08-24 14:07:12 | 1 | 2010 | 冀X11111 |
  15. +------+-----------+--------+---------------------+------+------+-----------+
  16. 2 rows in set (0.00 sec)
left join:  hphm='冀X11111' 放到 on 里面后,返回了 a 表的所有结果;放到where 后,返回交集。

点击(此处)折叠或打开

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

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

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

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

点击(此处)折叠或打开

  1. mysql> select * from vehicle a join xinxi b on a.hphm=b.hphm where a.hphm='冀X44444';
  2. Empty set (0.00 sec)
  3. 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存在的记录: 都返回为空

点击(此处)折叠或打开

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

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

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

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

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


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

阅读(478) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~