Using Subqueries In MySQL (part 1) - Apples And Oranges
(Page 7 of 7 )
As you've seen on the previous page, subqueries and joins coexist very nicely - but why restrict yourself only to inner joins? You can use pretty much any kind of join in combination with a subquery - and with a little bit of creative thinking, you can use the synergy between the two to perform some very sophisticated queries.
For example, you already know that you can obtain a list of all clients which have no entry in the "branches" table with a left join, as below:
mysql> SELECT cname FROM clients LEFT JOIN branches ON clients.cid =
branches.cid WHERE branches.bid IS NULL;
+-------------------+
| cname |
+-------------------+
| ABC Talent Agency |
+-------------------+
1 row in set (0.17 sec)
In this case, the query will retain all records on the left side of the join - the client list - and insert NULLs for every record on the right side - the branches - which does not meet the join condition. The end result? All clients without a branch office will be marked with a NULL value (which can then be isolated with an IS NULL test).
Another way to do this (and just so you know, I'm being deliberately perverse here because I want to demonstrate how joins can be used within
subqueries) is to use a right join with a subquery.
mysql> SELECT cname FROM clients WHERE cid = (SELECT clients.cid FROM
branches RIGHT JOIN clients ON clients.cid = branches.cid WHERE branches.bid
IS NULL);
+-------------------+
| cname |
+-------------------+
| ABC Talent Agency |
+-------------------+
1 row in set (0.22 sec)
In this case too, all records on the right side of the join - the clients - will be retained and missing branch office records for each client will be marked with NULLs. The subquery will return the corresponding client IDs, which can then be mapped to the "customers" table for the human-readable client name.
It's interesting to note that most of the time, subqueries can be written as joins, and vice-versa. Consider the following example, which lists the branch offices using the "Recruitment" service via a subquery,
mysql> SELECT bid FROM branches_services WHERE sid = (SELECT sid FROM
services WHERE sname = 'Recruitment');
+------+
| bid |
+------+
| 1011 |
| 1031 |
+------+
2 rows in set (0.22 sec)
and this next one, which does the same thing using a join.
mysql> SELECT bs.bid FROM branches_services AS bs, services AS s WHERE
s.sid = bs.sid AND s.sname = 'Recruitment';
+------+
| bid |
+------+
| 1011 |
| 1031 |
+------+
2 rows in set (0.17 sec)
More often than not, joins are faster than subqueries, because MySQL can optimize them better.
You can also write subqueries as self-joins, and vice-versa. Consider the following example, which lists all the branches in the same location as "JV Real Estate"'s corporate headquarters using a subquery,
mysql> SELECT bid, bdesc, bloc FROM branches WHERE bloc = (SELECT bloc
mysql> FROM
branches WHERE bid = 1101 AND cid = 110);
+------+----------------------+------+
| bid | bdesc | bloc |
+------+----------------------+------+
| 1011 | Corporate HQ | CA |
| 1042 | Branch Office (West) | CA |
| 1101 | Head Office | CA |
+------+----------------------+------+
3 rows in set (0.22 sec)
and this equivalent, which does the same thing using a self join.
mysql> SELECT table1.bid, table1.bdesc, table1.bloc FROM branches AS
table1, branches AS table2 WHERE table2.bid = 1101 AND table2.cid = 110 AND
table1.bloc = table2.bloc ;
+------+----------------------+------+
| bid | bdesc | bloc |
+------+----------------------+------+
| 1011 | Corporate HQ | CA |
| 1042 | Branch Office (West) | CA |
| 1101 | Head Office | CA |
+------+----------------------+------+
3 rows in set (0.16 sec)
And that's about it for this introductory segment. Over the last few pages, I introduced you to subqueries, one of the most powerful and useful new features in MySQL 4.1, and showed you how you can use them to filter SQL result sets. I demonstrated how subqueries can be used in both the WHERE and HAVING clause of outer SELECT queries, both in combination with MySQL's other comparison and logical operators and with MySQL's aggregate functions and GROUP BY clauses. Finally, I demonstrated how subqueries and joins can be used in combination with one another, and how many subqueries can be rewritten as joins (and vice-versa).
That's not all, though. Now that you (hopefully) know the basics, the second part of this tutorial will introduce you to more advanced uses of subqueries, including how to use them within a SELECT's query's FROM clause, with the IN operator and the EXISTS test, and with other MySQL data manipulation commands. All that and more, in the concluding segment of this article...so make sure you don't miss it!
Note: Examples are illustrative only, and are not meant for a production environment. Melonfire provides no warranties or support for the source code described in this article. YMMV!
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
