dadrobna/distributed-database-middleware
1
0
Fork 0
forked from docs/distributed-database-middleware
Code Pull Requests Activity
distributed-database-middle.../umn/source/sql_syntax/dml/select_subquery_syntax.rst
proposalbot 78ac771d84 Changes to ddm_umn from docs/doc-exports#613 (DDM UMN 1st version 
DDM UMN 1st v

Reviewed-by: Wagner, Fabian <fabian.wagner@t-systems.com>
Co-authored-by: proposalbot <proposalbot@otc-service.com>
Co-committed-by: proposalbot <proposalbot@otc-service.com>
2024-01-08 13:37:20 +00:00

2.3 KiB
Raw Permalink Blame History

original_name

ddm-08-0012.html

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.