The result of a SELECT statement may be used as a value in
another statement. For example the statement
SELECT region FROM bbc WHERE name = 'Brazil'
evaluates to 'Americas' so we can use this value to
obtain a list of all countries in the same region as
'Brazil'
1.
A nested SELECT using =
Results
Note that this is
not
the same thing as a join.
Two countries named Brazil?
Often the nested select is being compared against a single
value as in the above example. The phrase
(SELECT region FROM bbc WHERE name = 'Brazil')
should return exactly one region, namely 'Americas'. But
what would happen if we were to create a new European country
and name it Brazil?
This would result in a "run time error". The syntax of the
SQL is correct and our database engine starts to execute it. It
will fail when attempting to execute the outer statement - this
would be like executing the statement
SELECT name FROM bbc WHERE region = ('Americas',
'Europe')
.
Using multiple results in SQL
There are some SQL conditions which permit lists. For
example the "IN" operator tests a single value against a list
of values. The following will execute safely no matter how many
Brazils we have.
2.
A nested SELECT using IN
Results
There are other operators such as "ALL" and "ANY"; these may
be used in similar cases. I don't like them and I won't tell
you about them.