Home arrow MySQL arrow Page 7 - Using Subqueries In MySQL (part 1)

Apples And Oranges - MySQL

One of the most powerful new features in MySQL 4.1 is subqueries, which make it possible to nest multiple SQL queries within each other. This introductory segment of a two-part tutorial demonstrates how to use this new feature to build sophisticated data retrieval queries, illustrating (among other things) how to use subqueries in WHERE and HAVING clauses, with comparison and logical operators, with aggregate functions, and with inner, left, right and self joins.

TABLE OF CONTENTS:
  1. Using Subqueries In MySQL (part 1)
  2. Sub-Zero Code
  3. Turning The Tables
  4. Back To Basics
  5. Branching Out
  6. Having Your Code, And Eating It Too
  7. Apples And Oranges
By: RK Harigopal, (c) Melonfire
Rating: starstarstarstarstar / 14
July 24, 2003

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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!



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