--正确的sql
select t2.*,t1.dept_no from
(select * from salaries ) t2
left join
(select * from dept_manager) t1
on t1.emp_no=t2.emp_no
where t1.to_date='9999-01-01'
and t2.to_date='9999-01-01'
answer:
10002 72527 2001-08-02 9999-01-01 d001
10004 74057 2001-11-27 9999-01-01 d004
10005 94692 2001-09-09 9999-01-01 d003
10006 43311 2001-08-02 9999-01-01 d002
--错误的sql
select t2.*,t1.dept_no from
(select * from salaries where to_date='9999-01-01' ) t2
left join
(select * from dept_manager where to_date='9999-01-01' ) t1
on t1.emp_no=t2.emp_no
answer:
10001 88958 2002-06-22 9999-01-01
10002 72527 2001-08-02 9999-01-01 d001
10003 43311 2001-12-01 9999-01-01
10004 74057 2001-11-27 9999-01-01 d004
10005 94692 2001-09-09 9999-01-01 d003
10006 43311 2001-08-02 9999-01-01 d002
10007 88070 2002-02-07 9999-01-01
--
一直以为这两个sql是一样的,但是其实结果不一样,错误的sql其实是没有过滤掉最终结果中t1没有关联到的结果。