子查询可以出现在:where、from 语句中
1 子查询跟在where关键字后面
1.1 子查询果集1行1列,标量子查询
select * from t_user_apply where user_no = (select user_no from t_user_info where name = 'xiaoming')
在 "=" 情况下,其实和表内接连等价的:
select t_user_apply .* from t_user_apply inner join t_user_info on t_user_apply.user_no = t_user_info.user_no where t_user_info.name = 'xiaoming'
可以使用=, >, <, >=, <=, <> 来比较
1.2 子查询果集N行1列,列子查询
in (in太简单,不说了)
all、some、any;需要配合 =,>,<等使用;
如果有查询:
select file_size from t_file where user_no = '000001'; 假设搜索出来结果 10, 20, 30
那么对于all:
select * from t_file where file_size > all(select file_size from t_file where user_no = '000001')
等价于:
select * from t_file where file_size > 10 and file_size > 20 and file_size > 30
那么对于any:
select * from t_file where file_size > any(select file_size from t_file where user_no = '000001')
等价于:
select * from t_file where file_size > 10 or file_size > 20 or file_size > 30
some和any的含义是一样的
1.3 子查询果集是1行N列,行子查询
(1)> < 比较符号,只有第一的cloumns起效,其余无效,cloumns之间并没有and/or关系
(2)使用all、any、some非法,报错:
Error Code : 1241
Operand should contain 1 column(s)
(3)= 和 in 合法,表示多个cloumns全部满足
1.4 子查询结果是N行N列,表子查询
(1)=、>、< 非法,报错:
Error Code : 1242
Subquery returns more than 1 row
(2)all非法,报错
Error Code : 1241
Operand should contain 1 column(s
(3)in 合法
子查询果集 | 运算逻辑 | = | in | >< | all/any/some |
1行1列 | 合法, 表示column对应相等 | 合法 | 合法 | 合法 | |
N行1列 | 非法, 多行用in | 合法 | 非法, 需要配合all等 | 合法; 配合><=使用 | |
1行N列 | 合法, 每个column严格相等 | 合法, 每个column严格相等 | 合法, 但是只有第一column起效 | 非法 | |
N行N列 | 非法, 多行用in | 合法, 每个column严格相等(and);每行是or逻辑
| 非法 | 非法 |
本质上说:all/any/some是行之间的逻辑,但仅仅支持1 column;=、>、<是column之间逻辑,配合all、any、some可以支持多行,否则支持单行;只有in最强大,支持多行多列;
2 from后的子查询
from 后跟一个select出来的果集,这个select可以当成一个临时表,支持表所有特性
select tefr.file_name
from(
select employee_name, employee_no from t_employee_info where used_capacity > 1000
) as tem inner join t_employee_file_ref as tefr on tefr.employee_no = tem.employee_no
tem就理解成一个表
当然也可以把临时表放到join的后面
select
admin.*, !ISNULL(temp.id) as roleChecked
from
mgt_administrator as admin
left join (
select
xxx
) temp on admin.id = temp.id