昨天去客户方让客户小姐姐给我查询一段sql的时候,竟然发现sql子查询还可以这样写:
- sql子查询不仅仅可以出现在from 后面作为表结构,譬如:
select t1.company_id,t1.company_name,t1.department_id,t2.department_name
from company t1,
(select * from department where department_name like '%T%')t2
--子查询作为表放在from 后面
where t1.department_id=t2.department_id;
- sql子查询还可以作为条件放在where后面,譬如:
select t1.company_id,t1.company_name,t1.department_id,t2.department_name
from company t1,department t2
where t1.department_id=(
select distinct department_id
from department
where
department_name like '%IT%');
- sql子查询还可以出现在select后面作为字段,譬如:
select t1.company_id,t1.company_name,t1.department_id,
(select t2.department_name
from department t2
where t1.department_id=t2.department_id
)
from company t1;
--sql子查询放入select后面做字段使用
这里有个潜在问题,就是用作子查询的语句,查询结果只能是1:1 or n:1,不然就会出现主查询返回一条,但是子查询返回多行:
ORA-01427: single-row subquery returns more than one row