Home arrow MySQL arrow Page 3 - Using Subqueries In MySQL (part 2)

In And Out - MySQL

In this concluding segment of our MySQL subquery tutorial, find out how to do more with subqueries, including check for matching values with the IN operator, check for valid result sets with the EXISTS operator, derive new "virtual" tables for use in the FROM clause of outer queries, and UPDATE and DELETE records selectively.

TABLE OF CONTENTS:
  1. Using Subqueries In MySQL (part 2)
  2. Total Recall
  3. In And Out
  4. A Solitary Existence
  5. Turning The Tables
  6. Show Me The Money
  7. Adjusting For Inflation
  8. A New Broom
  9. Cleaning Up
By: RK Harigopal, (c) Melonfire
Rating: starstarstarstarstar / 16
July 31, 2003

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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)



 
 
>>> More MySQL Articles          >>> More By RK Harigopal, (c) Melonfire
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: