首页 > 代码库 > Mysql子查询
Mysql子查询
1单值(Scalar operand)
只有当外层(Parent)不为空时,才返回相应值;否则返回NULL。
note:For the subquery just shown, if t1
were empty, the result would be NULL
even though s2
is NOT NULL
.
2 用于操作符比较
[14.2.10.2 Comparisons Using Subqueries]
non_subquery_operand comparision_operator (subquery)
the comparision_operator is :=,>,>=,<,<=,!=,like ,<=>[和=功能相似,也可用于NULL值比较]
3 使用IN,ANY,SOME
operand comparison_operator ANY (subquery)
operand IN (subquery)
operand comoparison_operator SOME (subquery)
where comparison_operator is one of these operators:=,>, >=,<,<=,<>,!=
ANY,说明任何一个。只要右侧操作数中的任一个满足操作数条件,就为true.[ANY (不能是常量值)]
IN,包含[可以有常量值,多个用","分隔]。
SOME 与ANY 在功能上等价,只是在日常语法上不同。
4,使用ALL
operand comparison_operator ALL (subquery)
满足所有,才返回true.
5, 行子查询[Row subqueries]
值或列子查询(如上所示)返回一个值或符合条件的一列值;行子查询返回一行数据,可能有多个列;行子查询的合法操作符为:
=,>,<,>=,<=,<>,!=,<=>
只有多列同时满足操作符才返回true,否则为false or NULL.
6,使用 EXISTS OR NOT EXISTS
当子查询返回有数据时,EXISTS subquery 为true,AND NOT EXISTS subquery 为false.
7,关联子查询
是含有对外层查询有引用的查询。
8 用于From子句
9.子查询错误
10,优化子查询
【Development is ongoing,so no optimization tip is reliable for the long term.】开发正在进行时,从长期发展看没有可靠的优化技巧。
Mysql子查询