一、内连接
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>all(26,27,28); ---年龄必须大于子查询当中的最大值28岁才可以
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;