Nest Egg - MySQL

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.

>>> More MySQL Articles          >>> More By icarus, (c) Melonfire

blog comments powered by Disqus