select * from a, b where a.id = b.id; 对于外连接,Oracle中可以使用“(+)”来表示,9i可以使用LEFT/RIGHT/FULL OUTER JOIN,下面将配合实例一一介绍。 1. LEFT OUTER JOIN:左外关联 SELECT e.last_name, e.department_id, d.department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id); 等价于 SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id=d.department_id(+); 结果为:所有员工及对应部门的记录,包括没有对应部门编号department_id的员工记录。 2. RIGHT OUTER JOIN:右外关联 SELECT e.last_name, e.department_id, d.department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id); 等价于 SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id(+)=d.department_id; 结果为:所有员工及对应部门的记录,包括没有任何员工的部门记录。 3. FULL OUTER JOIN:全外关联 SELECT e.last_name, e.department_id, d.department_name FROM employees e FULL OUTER JOIN departments d ON (e.department_id = d.department_id); 结果为:所有员工及对应部门的记录,包括没有对应部门编号department_id的员工记录和没有任何员工的部门记录。
select *
from tzy_smda_temp sm,ttf_simhlr hlr
Where sm.xq=hlr.xq(+) and sm.sim >= hlr.sim_start and sm.sim <=hlr.sim_end
from tzy_smda_temp sm,ttf_simhlr hlr
Where sm.xq=hlr.xq(+) and sm.sim >= hlr.sim_start and sm.sim <=hlr.sim_endselect *
from tzy_smda_temp sm left join ttf_simhlr hlr
on sm.xq=hlr.xq and sm.sim >= hlr.sim_start and sm.sim <=hlr.sim_end 结果一样?
from tzy_smda_temp sm,ttf_simhlr hlr
Where sm.xq=hlr.xq(+) and sm.sim >= hlr.sim_start and sm.sim <=hlr.sim_endselect *
from tzy_smda_temp sm left join ttf_simhlr hlr
on sm.xq=hlr.xq and sm.sim >= hlr.sim_start and sm.sim <=hlr.sim_end
以上2句,我认为不等价,下面的应该等价:select *
from tzy_smda_temp sm left join ttf_simhlr hlr
on sm.xq=hlr.xq and sm.sim >= hlr.sim_start and sm.sim <=hlr.sim_end
select *
from tzy_smda_temp sm,ttf_simhlr hlr
Where sm.xq=hlr.xq(+) and sm.sim>= hlr.sim_start(+) and sm.sim<=hlr.sim_end(+)即:
tabel1 left join tabel2 tabel1为主表。
tabel1 RIGHT join tabel2 tabel2为主表。