在连接查询中,where子句是如何执行的是一个令人很困惑的事情,下面将举例说明,以防大家犯我犯过的错误。
有如下两张表,class班级表和student学生表,两表通过classcode班级号关联。而非常不巧的是两张表都有address字段,在class表中的address指的是班级所在地址,而student表中的address则是学生的家庭住址。
- create table CLASS
- (
- classcode NUMBER not null,
- classname VARCHAR2(100),
- address VARCHAR2(100)
- )
- create table STUDENT
- (
- studentid NUMBER not null,
- studentname VARCHAR2(100),
- classcode NUMBER,
- address VARCHAR2(100)
- )
- alter table STUDENT
- add constraint FK_STUDENT foreign key (CLASSCODE)
- references CLASS (CLASSCODE);
在两表中目前有如下数据
class表:
student表:
目前需要查询出坐落于Q山区Q山路班级中的学生,并显示其学生姓名,班级名称,班级地址,我们考虑使用连接查询。
对于我这样的菜鸟来说第一次写出的sql可能是这样的
- select c.classname,c.address,t.studentname from student t,class c
- where t.classcode=c.classcode
- and address='Q山区Q山路';
哦,我们可能会想到可能是由于两张表中都有address这个字段。于是我们为c.address取个别名。执行下面的sql:
- select c.classname,c.address as caddress,t.studentname from student t,class c
- where t.classcode=c.classcode
- and caddress='Q山区Q山路';
执行,再次报错,提示caddress这个是无效的字段。
奇怪,明明结果中有我们命名的caddress字段啊?
这就是连接查询where子句的执行方式问题了。在做连接查询后,执行where子句前首先会获得一个临时表,下面我将这个临时表show出来:
这个临时表拥有所有的班级与学生进行关联查询的结果,where 子句将对此临时表进行筛选。因此可以看出在此表中有两个address存在,通过address无法筛选,而第二个sql中caddress在此临时表中也不存在(字段别名是在最后阶段生效的)因此会报caddress无效的错误。因此要想获得正确的结果需要为address指定表,sql改写为:
- select c.classname,c.address as address,t.studentname from student t,class c
- where t.classcode=c.classcode
- and c.address='Q山区Q山路';
为了进一步验证以上结论下面我将sql进一步改写
- select tr.studentname,cr.caddress,cr.classname from
- (select t.studentname,t.address ,t.classcode from student t)tr,
- (select c.classcode,c.address as caddress,c.classname from class c )cr
- where tr.classcode=cr.classcode
- and caddress='Q山区Q山路';
根据上述原理此时where子句从以下临时表中进行筛选
此时可以通过caddress筛选班级地址,也可以通过address筛选学生地址,感兴趣的童鞋可以将where子句中的查询条件caddress改成addree看看结果是不是通过学生地址进行筛选了哦。
转载于:https://blog.51cto.com/owandering/1187756