As we have already discussed, relational databases
are table based. That is, all SQL commands are applied
to the contents of tables and then those results are displayed
as tables themselves. One great consequence of that is that
it is fairly easy to perform queries upon the virtual tables created by
other queries. Or, in jargonese, to perform subqueries.
Consider this example in which we want
to get a listing of all the clients
who made a transaction of greater than $150.00.
SELECT DISTINCT C_NAME, C_PHONE
FROM CLIENTS
WHERE EMP_NUM IN
(
SELECT C_NUM
FROM SALES
WHERE S_AMOUNT > 150.00
);
We would expect the following results:
C_NAME C_PHONE
--------------------------------
Jason Lim 456-7890
Stephen Petersen 167-3333
--------------------------------
As you can see, the subquery in the
WHERE clause would return a set containing "101"
and "103". Next, the top-level query would return
the two clients. In this case, the DISTINCT operator was not necessary
since we don't have many sales in our SALES table. However, for most
subqueries, it is useful to include the distinct so that you do not
get a client repeated for multiple sales. of course, you
might want that to :)
