9.19.1 SubselectsThis is NOT the latest copy of this book; click here for the latest version.
What do you do when you want to run a query based upon the results of another query? Without using subselects (also known as subqueries), you have two choices: run query one, then parse it using PHP and pass it into query two, or create a temporary table for the results of query one and then run query two on the temporary table.
However, both of these methods are clunky and slow. Ideally what you want to be able to do is run both queries through the server simultaneously and let the DBMS optimise it as best it can, and this is exactly what subselects allow you to do.
The following query demonstrates subselects on our dataset:
$result = mssql_query("SELECT * FROM Customers WHERE ID IN (SELECT DISTINCT Customer FROM Orders WHERE Purchase = 'Socks');");
The inner query, "SELECT Customer FROM Orders..." is executed first, and the results of that are piped back into the first query for use in the WHERE clause. Thus, the overall query will return all information on customers who have purchased socks.
Very often subselects can be done using clever joins, and this is a valid solution if you don't have subselects to hand. However, more often the join queries use GROUP BY clauses, which are inherently slow, and thus this option should be avoided if possible. In contrast, subselects use smaller, faster queries and builds up the results piece by piece.
Author's Note: Subselects are being added in MySQL 4.1, but it's important to note that the MySQL query cache does not cache each part of a query independently of the rest. That is, if query A uses subquery B and query C also uses subquery B, subquery B will not be cached: subqueries A and C will be. This is an irritation, particularly if subquery B is the bit that changes - hopefully it will be fixed soon.
|
Want to see this stuff in print? PHP in a Nutshell takes the core topics covered here, adds in thousands of edits from the editorial team and myself, and combines them to make an unbeatable reference for PHP programmers at all levels.
My latest book has hundreds more tips on how to use PHP, Apache, and MySQL, plus Perl, Python, shell scripts, performance tuning, and more!
|
