Subqueries can be nested to an unlimited depth in a FROM clause but to only” 255 levels in a WHERE clause. They can be used in the SELECT list and in the FROM, WHERE, andHAVING clauses of a query.

Inner subqueries will be executed first, which is actually logical.

Single row (scalar) subqueries

Here is an example that would return all the rows from table1 that have some_value bigger than the maximum some_value in the other table. We need to use the subquery to get the maximum some_value from the other table.

SELECT * FROM table1
WHERE some_value >=
(SELECT MAX(some_value) FROM table2);

Single-row subqueries can be used with these comparison operators: =, >, >=, <, <=, ;

Multiple rows subqueries

The result of these subqueries (in a WHERE clause) can only be compared with the following operators
IN, NOT IN, ANY, ALL, SOME. SOME and ANY mean one and the same.

In this case some_value from table1 needs to be bigger than all the values from table2 to be returned.

SELECT * FROM table1
WHERE some_value > ALL
(SELECT some_value FROM table2);

ANY or SOME would be opposite, they would require that some_value from table1 is bigger than at least one value from table2 to be selected.

SELECT * FROM table1
WHERE some_value > ANY
(SELECT some_value FROM table2);

IN (NOT IN is just a negation of it) returns rows only if the some_value in table1 is equal to the list of returned values by the table2

SELECT * FROM table1
WHERE some_value IN
(SELECT some_value FROM table2 WHERE some_condition = something);


Correlated subqueries have in them a value from the outer query and therefor for every row that inner query gets executed, also the outer query gets executed, in comparison to the previous ones, where inner query would execute completely and only then would the outer query be executed.

SELECT * FROM table1 t1
WHERE some_value IN
(SELECT some_value FROM table2 t2
WHERE t1.some_other_value = t2.some_other_value)

Note that it is not legal to use a subquery in the VALUES clause of an insert statement; this is fine:

insert into dates select sysdate from dual;

But this is not:

insert into dates (date_col) values (select sysdate fom dual);

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: