Back To Basics - MySQL

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)