Speaking SQL (part 2) - Nest Egg (
Page 8 of 8 )
SQL also allows
you to nest one query within another, such that the result of the inner query
provides data for the outer query. Such a query is referred to as a sub-query,
and it allows a great deal of flexibility when formulating long and complex
queries.
Let's suppose you want to find out who's rented "The Phantom
Menace" this weekend. You could of course perform a join, as described on the
previous page, and get the answer to your question. Or you could formulate a
subquery, which would look like this:
mysql> select fname, lname from members where member_id=(select member_id
from status where video_id=1);
In this case, SQL will first execute the inner query
SELECT member_id FROM status WHERE video_id=1;
+-----------+
| member_id |
+-----------+
| 1 |
+-----------+
and then assign the return value to the outer query, which
will display the result.
SELECT fname, lname FROM members WHERE member_id=1;
+-------+-------+
| fname | lname |
+-------+-------+
| John | Doe |
+-------+-------+
1 row in set (0.00 sec)
There is a limit on the number of subqueries you can use in a
single SQL statement, but it's usually quite a comfortable number. Note,
however, that mySQL does not currently support SQL subqueries.
And that's
about it. I hope this introduction to SQL helped you get some idea of how to go
about creating and using a database, and that you now have a better
understanding of the language. Till next time - stay healthy!
This article copyright Melonfire 2001. All rights reserved.
