昨天去客户方让客户小姐姐给我查询一段sql的时候,竟然发现sql子查询还可以这样写:
sql子查询不仅仅可以出现在from 后面作为表结构,譬如:
![在这里插入图片描述](https://img-blog.csdnimg.cn/90f81d854e4a476fbe7f5a30519007df.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA6KKL6byg6Ii56ZW_6Lev6aOe,size_20,color_FFFFFF,t_70,g_se,x_16)
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;
![在这里插入图片描述](https://img-blog.csdnimg.cn/f7bfefe64e3e48b894bcc3c8ec3cb408.png)
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%');
![在这里插入图片描述](https://img-blog.csdnimg.cn/cf40c197819743e4a61a90041a8aed93.png)
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
![在这里插入图片描述](https://img-blog.csdnimg.cn/1f32607444ae43429fcf1c7dd419cc3a.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA6KKL6byg6Ii56ZW_6Lev6aOe,size_20,color_FFFFFF,t_70,g_se,x_16)
|