|
|
|
|
|
|
|
|
|
|
- Overview
- Sub-query as selection condition
- Sub-query as file of the main query
A sub-query is a query whose result is used by another query. For example, you want to select the orders whose total amount is greater than the average amount of orders. The average amount of orders is not directly known. This average amount will be returned by another query (called sub-query). A sub-query can be used: - as selection condition.
- as file of the main query.
Limitations: - The ORDER BY statement is not allowed in the sub-queries.
- The sub-queries cannot be created in the query editor.
Sub-query as selection condition A sub-query can be used as selection condition. The authorized syntaxes are as follows: SELECT Item1 [, Item2]
FROM File1 [, File2]
WHERE Item1 Operator (SELECT Item1[, Item2]
FROM File1 [, File2]
[WHERE ...])
SELECT Item1 [, Item2]
FROM File1 [, File2]
WHERE [NOT] EXISTS (SELECT Item1[, Item2]
FROM File1 [, File2]
[WHERE ...]) In this syntax, <Operator> can correspond to: - ALL, ANY (or SOME), IN or NOT IN
- ALL: compare the value selected in the main query with all the values selected in the sub-query.
- NOT IN: checks whether the value selected by the main query does not belong to the list of values selected in the sub-query.
- IN: checks whether the value selected by the main query belongs to the list of values selected in the sub-query.
- ANY (or SOME): compare the value selected by the main query with any value selected in the sub-query.
- ALL: compare the value selected in the main query with all the values selected in the sub-query.
- a comparison operator (=, <, <=, >, >=, <> or!=).
- the combination between ALL, ANY (or SOME) and a comparison operator (=, <, <=, >, >=, <> or!=).
- ALL: compare the value selected in the main query with all the values selected in the sub-query.
- ANY (or SOME): compare the value selected by the main query with any value selected in the sub-query.
- ALL: compare the value selected in the main query with all the values selected in the sub-query.
Example: The following SQL code is used to select the number of the orders whose total amount is greater than the average amount of the orders: SELECT OrderNum
FROM ORDERS
WHERE Amount > (SELECT AVG(Amount)
FROM ORDERS) Sub-query as file of the main query A sub-query can be used as file of the main query. The authorized syntax is as follows: SELECT Item1 [, Item2]
FROM (SELECT Item1 FROM File1 [WHERE ...])
This page is also available for…
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|