RELATIONAL DATA RETRIEVAL: SQL - Fundamentals of Database Management Systems - page 87

be used. These latter circumstances are common enough and important enough to
include in this treatment of the SQL SELECT command.

Subqueries as Alternatives to Joins

Let's reconsider the first join example given

above:

''Find the name of the salesperson responsible for Customer Number 1525.''

If you methodically weave through the database tables to solve this, as we discussed
earlier in the topic, you start at the CUSTOMER table, find the record for Customer
Number 1525 and discover in that record that the salesperson responsible for
this customer is Salesperson Number 361. You then take that information to the
SALESPERSON table where you look up the record for Salesperson Number 361
and discover in it that the salesperson's name is Carlyle. Using a subquery, this
logic can be built into an SQL statement as:

SELECT SPNAME
FROM SALESPERSON
WHERE SPNUM=
(SELECT SPNUM
FROM CUSTOMER
WHERE CUSTNUM=1525);

and the result will again be:

SPNAME

Carlyle

Follow the way that the description given above of methodically solving
the problem is reconstructed as a SELECT statement with a subquery. Since the
innermost SELECT (the indented one), which constitutes the subquery, is considered
first, the CUSTOMER table is queried first, the record for Customer Number 1525
is found and 361 is returned as the SPNUM result. How do we know that only one
salesperson number will be found as the result of the query? Because CUSTNUM is
a unique attribute , Customer Number 1525 can only appear in one record and that
one record only has room for one salesperson number! Moving along, Salesperson
Number 361 is then fed to the outer SELECT statement. This, in effect, makes the
main query, that is the outer SELECT, look like:

SELECT SPNAME
FROM SALESPERSON
WHERE SPNUM=361;

and this results in:

SPNAME

Carlyle

Notice, by the way, that in the SELECT statement, there is only one semicolon
at the end of the entire statement, including the subquery.