ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / Editors / Query editor / SQL
  • Overview
  • Sub-query as selection condition
  • Sub-query as file of the main query
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
Others
Stored procedures
Overview
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 ...])
Minimum version required
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment

Last update: 05/26/2022

Send a report | Local help