SELECT Subquery Syntax¶
Subquery as Scalar Operand¶
Example
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¶
Syntax
non_subquery_operand comparison_operator (subquery)
comparison_operator: = > < >= <= <> != <=> like
Example
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¶
Syntax
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)
Example
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)¶
Syntax
SELECT ... FROM (subquery) [AS] tbl_name ...
Example
select id from (select id,name from test2 where id>1) a order by a.id;
Syntax Restrictions¶
Each derived table must have an alias.
A derived table cannot be a correlated subquery.
In some cases, correct results cannot be obtained using a scalar subquery. Using JOIN instead is recommended to improve query performance.
Using subqueries in the HAVING clause and the JOIN ON condition is not supported.
Row subqueries are not supported.