A Gentle Introduction to


Pick an engine:

CIA World Factbook

Using nested select statements

The result of a SELECT statement may be used as a value in another statement. For example the statement SELECT region FROM cia WHERE name = 'Brazil' evaluates to 'South America' so we can use this value to obtain a list of all countries in the same region as 'Brazil'

1 A nested SELECT using =

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 cia WHERE name = 'Brazil') should return exactly one region, namely 'South America'. 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 cia WHERE name = ('South America', 'Europe') . We would run into a similar problem if there were no country called Brazil in our table.

If Brazil were to disolve then our query would fail with an error - this may seem like a minor issue compared to the loss of a whole country, but, at the risk of seeming heartless, we would prefer that an empty table be returned under these tragic circumstances.

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

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.