Using Subqueries In MySQL (part 2) - In And Out (
Page 3 of 9 )
As you saw in the previous segment of this article, MySQL permits you to use
its numerous comparison operators to test the WHERE or HAVING clause of an outer
query against the result set generated by an inner query. Now, comparison
operators work great only so long as the subquery returns a result column
consisting of a single value. However, if you have a good memory, you'll
remember I said that a subquery can also return a single column of multiple
values. How does MySQL handle this?
Very simply, with the IN operator. The IN operator makes it possible to test
if a particular value exists in the result set, and perform the outer query if
the test is successful. Let me show you how.
Let's suppose I need a list of all services being used by a particular branch
office (say, branch ID 1031). Normally, I would need to get a list of all
service IDs for this branch,
mysql> SELECT sid FROM branches_services WHERE bid = 1031;
+-----+
| sid |
+-----+
| 2 |
| 3 |
| 4 |
+-----+
3 rows in set (0.16 sec)
and then look up each service ID in the "services" table for the
corresponding name.
mysql> SELECT sname FROM services WHERE sid = 2;
+-------------+
| sname |
+-------------+
| Recruitment |
+-------------+
1 row in set (0.28 sec)
mysql> SELECT sname FROM services WHERE sid = 3;
+-----------------+
| sname |
+-----------------+
| Data Management |
+-----------------+
1 row in set (0.17 sec)
mysql> SELECT sname FROM services WHERE sid = 4;
+----------------+
| sname |
+----------------+
| Administration |
+----------------+
1 row in set (0.11 sec)
With a subquery and the IN test, this becomes redundant.
mysql> SELECT sname FROM services WHERE sid IN (SELECT sid FROM
branches_services WHERE bid = 1031);
+-----------------+
| sname |
+-----------------+
| Recruitment |
| Data Management |
| Administration |
+-----------------+
3 rows in set (0.27 sec)
In this case, MySQL will select only those records from the "services" table
which match the service ID collection returned by the subquery.
A variant of this might be to obtain a list of all branches using the
"Accounting" service (service ID 1).
mysql> SELECT bdesc FROM branches WHERE bid IN (SELECT bid FROM
branches_services WHERE sid = 1);
+-----------------------+
| bdesc |
+-----------------------+
| Corporate HQ |
| Accounting Department |
| Branch Office (East) |
| Branch Office (West) |
| Head Office |
+-----------------------+
5 rows in set (0.17 sec)
Hmmm...not too useful. What might be nice here is the customer name for each
branch as well - something easily accomplished by adding a quick join.
mysql> SELECT cname, bdesc FROM branches, clients WHERE branches.bid IN
(SELECT bid FROM branches_services WHERE sid = 1) AND clients.cid = branches.cid;
+-----------------------------+-----------------------+
| cname | bdesc |
+-----------------------------+-----------------------+
| JV Real Estate | Corporate HQ |
| JV Real Estate | Accounting Department |
| Rabbit Foods Inc | Branch Office (East) |
| Rabbit Foods Inc | Branch Office (West) |
| Sharp Eyes Detective Agency | Head Office |
+-----------------------------+-----------------------+
5 rows in set (0.16 sec)
Want just the customer list? Add the DISTINCT keyword,
mysql> SELECT DISTINCT cname FROM branches, clients WHERE branches.bid
mysql> IN
(SELECT bid FROM branches_services WHERE sid = 1) AND clients.cid = branches.cid;
+-----------------------------+
| cname |
+-----------------------------+
| JV Real Estate |
| Rabbit Foods Inc |
| Sharp Eyes Detective Agency |
+-----------------------------+
3 rows in set (0.17 sec)
or hey, just rewrite the query using the IN test again.
mysql> SELECT cname FROM clients WHERE cid IN (select cid from branches
where bid IN (SELECT bid FROM branches_services WHERE sid = 1));
+-----------------------------+
| cname |
+-----------------------------+
| JV Real Estate |
| Rabbit Foods Inc |
| Sharp Eyes Detective Agency |
+-----------------------------+
3 rows in set (0.22 sec)
Wanna mix things up a little? Let's say I want a list of high-value
clients
- all those with individual branch offices having a monthly bill of
$2000 or more. I can get this information (among other ways) by using a subquery
with a join, a GROUP BY clause and the IN operator...all at once, while
simultaneously balancing a slice of pizza.
mysql> SELECT cname FROM clients WHERE cid IN (select cid from branches
where bid IN (SELECT bid FROM branches_services as bs, services as s where bs.sid
= s.sid group by bid having sum(sfee) >= 2000));
+------------------+
| cname |
+------------------+
| JV Real Estate |
| Rabbit Foods Inc |
+------------------+
2 rows in set (1.32 sec)
You can use the NOT keyword to reverse the results of the IN operator - or,
in other words, to return those records not matching the result collection
generated by a subquery. If you had to rewrite the example above using the NOT
operator, it would look something like this:
mysql> SELECT cname FROM clients WHERE cid IN (select cid from branches
where bid NOT IN (SELECT bid FROM branches_services as bs, services as s where
bs.sid = s.sid group by bid having sum(sfee) < 2000));
+------------------+
| cname |
+------------------+
| JV Real Estate |
| Rabbit Foods Inc |
+------------------+
2 rows in set (1.54 sec)
/ 16 
