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

Back To Basics - 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

You already know that you can obtain a complete list of all the records in a table with a simple SELECT * - for example, a list of all clients:


mysql> SELECT * FROM clients;
+-----+-----------------------------+
| cid | cname |
+-----+-----------------------------+
| 101 | JV Real Estate |
| 102 | ABC Talent Agency |
| 103 | DMW Trading |
| 104 | Rabbit Foods Inc |
| 110 | Sharp Eyes Detective Agency |
+-----+-----------------------------+
5 rows in set (0.22 sec)

You can also attach a join and a WHERE clause to the SELECT statement in order to filter the list of records down to only those matching specific criteria - for example, a list of all clients with branch offices in California only:


mysql> SELECT cname, bdesc, bloc FROM clients, branches WHERE
mysql> clients.cid =
branches.cid AND branches.bloc = 'CA';
+-----------------------------+----------------------+------+
| cname | bdesc | bloc |
+-----------------------------+----------------------+------+
| JV Real Estate | Corporate HQ | CA |
| Rabbit Foods Inc | Branch Office (West) | CA |
| Sharp Eyes Detective Agency | Head Office | CA |
+-----------------------------+----------------------+------+
3 rows in set (0.00 sec)

How about something a little more involved? Let's say I need a list of all the branch offices belonging to "Rabbit Foods Inc". Now, I could do this by running two SELECT queries, one after another, to first get the customer ID of "Rabbit Foods Inc", and then using that ID (104) in another query to get the list of branch offices linked to that customer,


mysql> SELECT cid FROM clients WHERE cname = 'Rabbit Foods Inc';
+-----+
| cid |
+-----+
| 104 |
+-----+
1 row in set (0.17 sec)

mysql> SELECT bdesc FROM branches WHERE cid = 104;
+----------------------+
| bdesc |
+----------------------+
| Branch Office (East) |
| Branch Office (West) |
+----------------------+
2 rows in set (0.17 sec)

by equi-joining the "clients" and "branches" tables,


mysql> SELECT bdesc FROM branches, clients WHERE clients.cid =
mysql> branches.cid
AND clients.cname = 'Rabbit Foods Inc';
+----------------------+
| bdesc |
+----------------------+
| Branch Office (East) |
| Branch Office (West) |
+----------------------+
2 rows in set (0.22 sec)

or with a subquery.


mysql> SELECT bdesc FROM branches WHERE cid = (SELECT cid FROM clients
WHERE cname = 'Rabbit Foods Inc');
+----------------------+
| bdesc |
+----------------------+
| Branch Office (East) |
| Branch Office (West) |
+----------------------+
2 rows in set (0.22 sec)

Thus, a subquery makes it possible to combine two or more queries into a single statement, and use the results of one query in the conditional clause of the other. Subqueries are usually regular SELECT statements, and are separated from their parent query by parentheses, as in the example above.

A subquery must return a single column of results, or else MySQL will not know how to handle the result set. Consider the following example, which demonstrates by having the subquery return a two-column result set:


mysql> SELECT bdesc FROM branches WHERE cid = (SELECT cid, cname FROM
clients WHERE cname = 'Rabbit Foods Inc');
ERROR 1239: Cardinality error (more/less than 1 columns)

You can nest subqueries to any depth, so long as the basic rules above are followed. Consider the following example, which demonstrates by listing the services used by Sharp Eyes Detective Agency:


mysql> SELECT sname FROM services WHERE sid = (SELECT sid FROM
branches_services WHERE bid = (SELECT bid FROM branches WHERE cid = (SELECT cid FROM clients WHERE cname = 'Sharp Eyes Detective Agency')));
+------------+
| sname |
+------------+
| Accounting |
+------------+
1 row in set (0.28 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: