有这样两张表,如下图:
stu_info stu_score
id name id score stu_id
1 刘思宇 1 99 2
2 王康 2 60 1
3 费楠 3 45 5
4 欧凤奇 4 99 3
5 邱毅 5 59 4
【SQL语句下载】
要实现这样一种查询:查询出成绩不及格的学生的姓名:
1、in:select si.name from stu_info si where si.id in (select stu_id from stu_score where score < 60 );
not in:select si.name from stu_info si where si.id not in (select stu_id from stu_score where score >= 60 );
2、exists:select si.name from stu_info si where exists (select stu_id from stu_score where score < 60 and si.id = stu_id);
not exists:select si.name from stu_info si where not exists (select stu_id from stu_score where score >= 60 and si.id = stu_id);
3、left join...on:select si.name from stu_info si left join stu_score ss on si.id = ss.stu_id where ss.score < 60;
或
select si.name from stu_info si left join stu_score ss on si.id = ss.stu_id and ss.score < 60 where ss.stu_id is not null;
或
select si.name from stu_info si left join stu_score ss on si.id = ss.stu_id and ss.score >= 60 where ss.stu_id is null;
上面1、2和3都可以实现这一查询效果,通常使用含有exists关键字的SQL语句——因为exists的SQL性能略优与in;最好使用left join...on SQL语句——因为左连接的SQL语句没有使用子查询,SQL性能最好;
扩展:将stu_scroe表结构做一修改:
stu_score
id course_name score stu_id
1 语文 99 2
2 语文 60 1
3 语文 45 5
4 语文 99 3
5 语文 59 4
6 数学 89 2
7 数学 70 1
8 数学 45 5
9 数学 69 3
10 数学 59 4
【SQL语句下载】
要实现这样一种查询:使用左连接查询各科成绩均不及格的学生的姓名:
如果这时还执行上面3条left join...on 语句你会发现前两条,即“select si.name from stu_info si left join stu_score ss on si.id = ss.stu_id where ss.score < 60;”和“select si.name from stu_info si left join stu_score ss on si.id = ss.stu_id and ss.score < 60 where ss.stu_id is not null;”会出现四条两两重复的数据,如下:
name
欧凤奇
欧凤奇
邱毅
邱毅
如果执行上面3条left join...on 语句中的第3条,即“select si.name from stu_info si left join stu_score ss on si.id = ss.stu_id and ss.score >= 60 where ss.stu_id is null;”则可以达到查询效果,其实如果你把该语句中的where条件查询去掉查询的记过如下:
name
刘思宇
刘思宇
王康
王康
费楠
费楠
欧凤奇
邱毅
即也出现了数据两两重复的现象;
总之一句话:再使用left join...on会出现数据两两重复的现象,为什么呢?如果讲上面三条含left join...on的SQL语句做一修改——添加一个distinct关键字:
select distinct si.name from stu_info si left join stu_score ss on si.id = ss.stu_id where ss.score < 60;
或
select distinct si.name from stu_info si left join stu_score ss on si.id = ss.stu_id and ss.score < 60 where ss.stu_id is not null;
或
select distinct si.name from stu_info si left join stu_score ss on si.id = ss.stu_id and ss.score >= 60 where ss.stu_id is null;
添加完该关键字后就会发现这时的查询结果才是我们想要的,可是上面为什么会出现数据两两重复的现象呢?为什么加上distinct关键字就可以了呢?呵呵呵,之所以出现数据两两重复的现象是因为那样的left join...on语句产生了笛卡尔积,而使用distinct关键字可以消除笛卡尔积 产生的影响。
笛卡尔积:
待续。。。
distinct:
待续。。。