The only difference to a simple SELECT is, that it is enclosed in parenthesis.
Depending on the type of the created result there are three classes of subqueries: Every type can be used on all positions where the type it stands for may occur: the scalar value subquery where a single value may occur, the row subquery where a single row may occur and the table subquery where a table may occur.
A value list is a list of simple values that can be either constants or parameterized values.
Therefore you can only specify a noncorrelated scalar subquery that returns a value of a primitive data type in a value list.
This query finds out all the employees who livein the same city and country as customers.Correlated subqueries have a correlation to the surrounding query by the fact that they use values from the surrounding query within the subquery.Non-correlated subqueries are independent from the surrounding query.The subquery potentially computes a different , the subquery must include at least one equality comparison between the columns of the inner and outer query blocks. Examples: This example illustrates how subqueries can be used in the SELECT avg(t1.x), max(t2.y) FROM (SELECT id, cast(a AS DECIMAL(10,5)) AS x FROM raw_data WHERE a BETWEEN 0 AND 100) AS t1 JOIN (SELECT id, length(s) AS y FROM raw_data WHERE s LIKE 'A%') AS t2; USING (id); Usage notes: If the same table is referenced in both the outer and inner query blocks, construct a table alias in the outer query block and use a fully qualified name to distinguish the inner and outer table references: clauses are rewritten into join queries.
All syntax is available for both correlated and uncorrelated queries, except that the clause cannot be used with an uncorrelated subquery. Depending on the syntax, the subquery might be rewritten to an outer join, semi join, cross join, or anti join.That is, the aliased SSQ reference cannot be nested in another SELECT block that is defined within the referencing expression.