一.sql语句中left join、inner join中的on与where的区别
0.各种join操作的概念和作用
left join :左连接,返回左表中所有的记录以及右表中连接字段相等的记录。
right join :右连接,返回右表中所有的记录以及左表中连接字段相等的记录。
inner join: 内连接,又叫等值连接,只返回两个表中连接字段相等的行。
full join:外连接,返回两个表中的行:left join + right join。
cross join:结果是笛卡尔积,就是第一个表的行数乘以第二个表的行数。
关键字: on
数据库在通过连接两张或多张表来返回记录时,都会生成一张中间的临时表,然后再将这张临时表返回给用户。
在使用left jion时,on和where条件的区别如下:
① on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
② where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。
有如下两个表:
表1:
表2:
1.left join 和 where 的区别
sql语句1:select a., b. from a left join b on a.id = b.id and a.type = 1;
sql语句2:select a., b. from a left join b on a.id = b.id where a.type = 1;
sql语句3:select a., b. from a left join b on a.id = b.id and b.class = 1;
Sql语句1的执行结果:
sql语句2的执行结果为:
sql语句3的执行结果为:
由sql语句1可见,left join 中左表的全部记录将全部被查询显示,on 后面的条件对它不起作用,除非再后面再加上where来进行筛选,这就是sql语句2了;
由sql语句3可见,on后面的条件中,右表的限制条件将会起作用。
2.inner join 和 where 的比较
sql语句4:select a., b. from a inner join b on a.id = b.id and a.type = 1;
sql语句5:select a., b. from a inner join b on a.id = b.id where a.type = 1;
sql语句6:select a., b. from a, b where a.id = b.id and a.type = 1;
sql语句7:select a., b. from a, b where a.type = 1 and a.id = b.id;
这四条语句的执行结果一样,如下:
由此可见,inner join 中on后面的限制条件将全部起作用,这与where的执行结果是一样的。另外,where语句与inner join确实能得到相同的结果,只是效率不同
3.分析过程:
假设有两张表:
表1:tab2
tab1.id | tab1.size |
---|---|
1 | 10 |
2 | 20 |
3 | 30 |
表2:tab2
tab2.size | tab1.name |
---|---|
10 | AAA |
20 | BBB |
20 | CCC |
两条SQL:
1、select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’
2、select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)
第一条SQL的过程:
1、中间表
on条件:
tab1.size = tab2.size
tab1.id | tab1.size | tab2.size | tab2.name |
---|---|---|---|
1 | 10 | 10 | AAA |
2 | 20 | 20 | BBB |
2 | 20 | 20 | CCC |
3 | 30 | (null) | (null) |
2、再对中间表过滤
where 条件:
tab2.name=’AAA’
tab1.id | tab1.size | tab2.size | tab2.name |
---|---|---|---|
1 | 10 | 10 | AAA |
第二条SQL的过程:
1、中间表
on条件:
tab1.size = tab2.size and tab2.name=’AAA’
(条件不为真也会返回左表中的记录)
tab1.id | tab1.size | tab2.size | tab2.name |
---|---|---|---|
1 | 10 | 10 | AAA |
2 | 20 | (null) | (null) |
3 | 30 | (null) | (null) |
其实以上结果的关键原因就是left join,right join,full join的特殊性,不管on上的条件是否为真都会返回left或right表中的记录,full则具有left和right的特性的并集。 而inner jion没这个特殊性,则条件放在on中和where中,返回的结果集是相同的。
========================================分割线
二.最近面试遇到的两个面试题
有如下两个表:
表a:
表b:
1.如下sql语句对应的结果:
SELECT a.key1,b.key1 FROM a LEFT OUTER JOIN b ON (a.key1 = b.key1) WHERE a.ds = '2009-07-07' AND b.ds = '2009-07-07';
2.如下sql语句对应的结果:
```c
SELECT a.key1,b.key1 FROM a LEFT OUTER JOIN b ON (a.key1 = b.key1 AND b.ds = ‘2009-07-07’ AND a.ds = ‘2009-07-07’);
where与having的区别
1、用COUNT()函数和GROUP BY语句可以统计同一emp_no值的记录条数
2、根据题意,输出的变动次数为t,故用AS语句将COUNT(emp_no)的值转换为t
3、由于COUNT()函数不可用于WHERE语句中,故使用HAVING语句来限定t>15的条件
4.where中不可使用聚合函数 ,聚合函数是针对结果集进行的,但是where条件并不是在查询出结果集之后运行,所以主函数放在where语句中,会出现错误,
5.having不一样,having是针对结果集做筛选的,所以我们一般吧组函数放在having中,用having来代替where,having一般跟在group by之后
WHERE语句在GROUP BY语句之前;SQL会在分组之前计算WHERE语句。
HAVING语句在GROUP BY语句之后;SQL会在分组之后计算HAVING语句。
group by 与 distinct区别
对于distinct与group by的使用:
1、当对系统的性能高并数据量大时使用group by
2、当对系统的性能不高时使用数据量少时两者皆可
3、尽量使用group by