10:40 2013-08-29
JOIN ON...AND
1 A left join B on A.col1=B.col1 and A.col2=xx 2 A left join B on A.col1=B.col1 where A.col2=xx
前面一种情况A.col2=xx是作为与 B的关联条件,满足on条件的返回B值,否则B为NULL(只影响B是否为NULL)
后面一种情况A.col2=xx是作为where筛选条件,满足where条件的A才能作为左表(影响A的行数)
在left join和right join的时候on条件不会删减“主”表的数据
mysql> select * from t1; +----+------+ | id | cnum | +----+------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | | 4 | 400 | +----+------+ mysql> select * from t2; +----+--------+-------+ | id | weight | exist | +----+--------+-------+ | 2 | 22 | 0 | | 4 | 44 | 1 | | 5 | 55 | 0 | | 6 | 66 | 1 | +----+--------+-------+ mysql> select * from t1 a left join t2 b on a.id=b.id and b.weight!=44 and b.exist=0 where b.id is null; +----+------+------+--------+-------+ | id | cnum | id | weight | exist | +----+------+------+--------+-------+ | 1 | 100 | NULL | NULL | NULL | | 3 | 300 | NULL | NULL | NULL | | 4 | 400 | NULL | NULL | NULL | +----+------+------+--------+-------+
EXISTS子查询
exists用于选择条件中一般都用作相关子查询
exists子句根据其内查询语句的结果集空或者非空,返回一个布尔值。
1 select top 20 * from DB_1.dbo.MF_MO_Z 2 where not exists (select top 10 * from DB_1.dbo.MF_MO_Z)
上面子查询是无关子查询,根据语句not exists返回的是false,因此整个语句返回为空。
1、先判断是无关还是相关子查询
无关子查询:子查询本身能直接执行
相关子查询:子查询执行本身会报错,因为引用到了外部查询的表
2、按照下面的说明,分析语句返回结果
无关:只要后面有一条记录,条件就成立
相关:将外查询表的每一行,代入内查询作为检验,如果内查询返回的结果取非空值,则EXISTS子句返回TRUE,这一行可作为外查询的结果行,否则不能作为结果。
联接(Join)
嵌套循环(Nested Loops Join)
对于顶部(外部)输入的每一行,扫描底部(内部)输入,然后输出匹配的行。
算法复杂度:inner table*outer table;外部表executed一次,内部表executedcount(returned外部表)次
适用于:外部表较小,内部表在join key上有索引
合并联接(Merge Join)
从两个已进行了相应排序的输入表中, 使用其排序顺序对行进行匹配。
算法复杂度:最大是大的那个记录数;both inputs executed only once
适用于:There is an equality operator on the join predicate(联接谓词),and their inputs are sorted.
哈希匹配(Hash Join)
使用来自顶部(build 构建)输入的每一行生成哈希表,使用来自底部(probe 探针)输入的每一行探测该哈希表,然后输出所有匹配的行。
算法复杂度:build+probe;both inputs executed only once
适用于:输入数据集较大,且未排序