Logo         Community
  Trog
Services
The Company
Community
Columns
Your Account
Contact Us
 
 
Using Subqueries In MySQL (part 2)
Do more with subqueries with the IN and EXISTS operators.

| In And Out |

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
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)



How to do Everything with PHP & MySQL
How to do Everything with PHP & MySQL, the best-selling book by Melonfire, explains how to take full advantage of PHP's built-in support for MySQL and link the results of database queries to Web pages. You'll get full details on PHP programming and MySQL database development, and then you'll learn to use these two cutting-edge technologies together. Easy-to-follow sample applications include a PHP online shopping cart, a MySQL order tracking system, and a PHP/MySQL news publishing system.

Read more, or grab your copy now!


previous page more like this  print this article  next page
 
Search...
 
In trog...
Logging With PHP
Building A Quick-And-Dirty PHP/MySQL Publishing System
Output Buffering With PHP
Date/Time Processing With PHP
Creating Web Calendars With The PEAR Calendar Class
more...
 
In the hitg report...
Crime Scenes
Animal Attraction
Lord Of The Strings
more...
 
In boombox...
Patience - George Michael
Think Tank - Blur
My Private Nation - Train
more...
 
In colophon...
Hostage - Robert Crais
The Dead Heart - Douglas Kennedy
Right As Rain - George Pelecanos
more...
 
In cut!...
American Chai
The Core
Possession
more...
 
Find out how you can use this article on your own Web site!


Copyright © 1998-2018 Melonfire. All rights reserved
Terms and Conditions | Feedback