Nested queries

Conditions are usually made of an attribute, a value and an operator that forms the condition (eg. Name="John"). But the values themselves don't have to be constants, they can be the result of another sub-query. We then talk about nested queries. Using the IN-operator nested queries can be as deep as necessary.

There are three types of sub-queries that differ in their result:

Sub-queries that return a value (one column and one row)
Sub-queries that return a row
Sub-queries that return several rows

If only one value or row is returned the normal comparison operators can be used.

If more than one row is returned special operators are used:

IN examines whether the value exists in the sub-query
<Comparison operator> ALL the condition must return TRUE for all rows in the sub-query
<Comparison operator> ANY (SOME) the condition must return TRUE for at least one row in the sub-query

Nested queries

Sub-queries can also be used in the FROM part of a query. Thus, a relation can be compiled specifically for a request. The sub-query must be assigned a name with AS: (<Sub-query>) AS <Name>