内连接、外连接与子查询
一、内连接
1、内连接与子查询结果一致,但内连接效率高
select a.dept_name, a.id, b.prem_codes, c.scy_lvl
from T_DA_MGR_ORG a
join T_DA_ORG_SCY_REL b
on a.id = b.dept_id
join T_DA_SCY_LVL c
on b.scy_lvl = c.scy_lvl
select a.dept_name, a.id, b.prem_codes, c.scy_lvl
from T_DA_MGR_ORG a, T_DA_ORG_SCY_REL b, T_DA_SCY_LVL c
where a.id = b.dept_id
and b.scy_lvl = c.scy_lvl
2、单行子查询 ,基于集合测试的嵌套子查询 where =、>、 >= 、= 、 <、<=、between and、and、or、in
select * from stuinfo t
where t.classno in
(select b.classno from class b where b.classname = '信息科学2班(18)' );
3、多行子查询 ,要求子查询只能返回单个值 ANY、ALL、some
select * from stuinfo t where t.age > any(26,27,28); ---年龄只要大于当中子查询的最小值26岁即可 select * from stuinfo t where t.age > (select min(age)from stuinfo)
select * from stuinfo t where t.age > all (26,27,28); ---年龄必须大于子查询当中的最大值28岁才可以 select * from stuinfo t where t.age > (select max(age)from stuinfo)
4、为计算字段使用子查询
select cust_name,
cust_state,
(select count(*) from orders where orders.id = customers.cust_id) as orders
from customers
二、外连接
1、左外连接
select a.dept_name, a.id, b.prem_codes, c.scy_lvl
from T_DA_MGR_ORG a
left join T_DA_ORG_SCY_REL b
on a.id = b.dept_id
left join T_DA_SCY_LVL c
on b.scy_lvl = c.scy_lvl
select a.dept_name, a.id, b.prem_codes, c.scy_lvl
from T_DA_MGR_ORG a, T_DA_ORG_SCY_REL b, T_DA_SCY_LVL c
where a.id = b.dept_id(+)
and b.scy_lvl = c.scy_lvl(+)
2、右外连接
select a.dept_name, a.id, b.prem_codes, c.scy_lvl
from T_DA_MGR_ORG a
right join T_DA_ORG_SCY_REL b
on a.id = b.dept_id
right join T_DA_SCY_LVL c
on b.scy_lvl = c.scy_lvl
select a.dept_name, a.id, b.prem_codes, c.scy_lvl
from T_DA_MGR_ORG a, T_DA_ORG_SCY_REL b, T_DA_SCY_LVL c
where a.id(+) = b.dept_id
and b.scy_lvl (+)= c.scy_lvl
3、全外连接
select a.*, b.stuid, b.stuname
from 表1 a
full join 表2 b
on a.stuid = b.stuid;