Subqueries

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. :)

Joins
Introduction to Databases for the Web | Table of Contents
Adding Data