文档首页/ 分布式数据库中间件 DDM/ 用户指南/ SQL语法/ DML/ SELECT Subquery Syntax
更新时间：2022-07-18 GMT+08:00
查看PDF
分享

SELECT Subquery Syntax

The Subquery as Scalar Operand

示例

SELECT (SELECT id FROM test1 where id=1);         
SELECT (SELECT id FROM test2 where id=1)FROM test1;              
SELECT UPPER((SELECT name FROM test1 limit 1)) FROM test2;

Comparisons Using Subqueries

语法

non_subquery_operand comparison_operator (subquery)
comparison_operator ：= > < >= <= <> != <=> like

示例

select name from test1 where id > (select id from test2 where id=1);
select name from test1 where id = (select id from test2 where id=1);
select id from test1 where name like (select name from test2 where id=1);

Subqueries with ANY, IN, NOT IN, SOME,ALL,Exists,NOT Exists

语法

operand comparison_operator SOME (subquery)
operand comparison_operator ALL (subquery)
operand comparison_operator ANY (subquery) 
operand IN (subquery) 
operand not IN (subquery)
operand exists (subquery)
operand not exists (subquery)

示例

select id from test1 where id > any (select id from test2);
select id from test1 where id > some (select id from test2);
select id from test1 where id > all (select id from test2);
select id from test1 where id in (select id from test2);
select id from test1 where id not in (select id from test2);
select id from test1 where exists (select id from test2 where id=1);
select id from test1 where not exists (select id from test2 where id=1);

Derived Tables (Subqueries in the FROM Clause)

语法

SELECT ... FROM (subquery) [AS] tbl_name ...

示例

select id from (select id,name from test2 where id>1) a  order by a.id;

语法限制

  • Derived Tables 必须拥有一个别名。
  • Derived Tables 不可以成为 Correlated Subqueries，即不能包含子查询外部表的引用。
  • 标量子查询在一些场景下当前不能得到正确结果，建议改写为join，同时可提高性能。
  • 不支持 HAVING 子句中的子查询，JOIN ON 条件中的子查询。
  • 不支持Row Subqueries。
父主题： DML