This is the sixth of our on-going series of articles that explain some the new features in MySQL 4.1, which is now available as a generally-available (GA, or production) release.
Effective with MySQL version 4.1, there are two ways in which one can get data from multiple tables in a single query: with a join and with a subquery. For example, assume you have the following tables:
CREATE TABLE clients ( clno INT, fname VARCHAR(15), lname VARCHAR(15), job VARCHAR(15), account_balance DECIMAL(7,2)); INSERT INTO clients VALUES (10, 'sam','smith','auditor',5525.75), (20,'james','jones','manager',8960.25); CREATE TABLE firms ( clno INT, company VARCHAR(15), city VARCHAR(15)); INSERT INTO firms VALUES (10,'abc co','leduc'), (20,'def ltd','nisku'), (30,'ghi inc','nisku');
The following query uses a join to get all available information for client number 10:
SELECT fname, lname, city, job, company, account_balance FROM clients c, firms f WHERE c.clno = f.clno AND c.clno = 10;
But it isn't always possible to use a join to get the information you may need. For example, suppose you need all available information on the client with the largest account balance. The following query, which may look as if it should return the required information, instead returns an error:
SELECT fname, lname, city, job, company, account_balance FROM clients c, firms f WHERE c.clno = f.clno AND c.account_balance = MAX(c.account_balance);
The reason for the error — invalid use of group function — is that the aggregate function, MAX
, is disallowed in the WHERE
clause as shown. This is where the second method of getting data from multiple tables in a single query — the subquery — comes to the rescue. In this article, I'll briefly describe the subquery functionality added to MySQL in version 4.1
Subqueries are SELECTs inside Parentheses
Simply put, a subquery is a SELECT
statement that is written inside another SQL statement (which is often, but does not have to be, another SELECT
). To distinguish the subquery (or inner query) from its enclosing query (or outer query), it must be enclosed within parentheses. Here is an example:
SELECT * FROM clients WHERE clno IN -- outer query (SELECT clno FROM firms WHERE city = 'leduc'); -- inner query
This query will return all rows of the clients
table which have the same clno
value as the rows of the firms
table having a city
value equal to 'leduc'
. To get the result, the DBMS first evaluates the inner query, to find the clno
value for every row in the firms
table where city
is equal to 'leduc'
. It then compares these clno
values to the rows of the clients
table, returning every row where the clno
values match. Since there is only one row in firms
which matches the subquery condition, the subquery example — in effect — is equivalent to this query:
SELECT * FROM clients WHERE clno = 10;
The subquery example can also, of course, be written as a join:
SELECT c.clno, fname, lname, job, account_balance FROM clients c INNER JOIN firms f USING (clno) WHERE city = 'leduc';
However, as already noted, the same can not be said of this subquery (translation: which client has the highest clno
value?):
SELECT fname, lname FROM clients WHERE clno = (SELECT MAX(clno) FROM firms);
If the inner query returns an empty set, the result of a subquery may appear to be incorrect. For example, consider this query:
SELECT * FROM clients WHERE clno = (SELECT clno FROM firms WHERE city = 'gibbons');
If the inner query is run alone, it is clear that the result is zero rows: there are no rows in the firms
table with a city
equal to 'gibbons'
. But "empty set" cannot be compared to the values of a column. The SQL Standard thus requires that the result of a subquery, which evaluates to zero rows, is NULL
. And since nothing is equal to NULL
, the query returns an "Empty set" message (i.e. zero rows).
It is common to say that the subquery is nested in the outer query. MySQL supports the nesting of subqueries within other subqueries, to a great depth.
Types of Subqueries
There are three types of subqueries, distinguished from one another by the result — how many rows and columns — they return.
-
If a subquery can return exactly one column and one row, it is known as a scalar subquery. A scalar subquery is legal everywhere that a regular scalar value (e.g. a column value or literal) is legal in an SQL statement. It is usually found in a
WHERE
clause, immediately after a comparison operator. -
If a subquery can return multiple columns and exactly one row, it is known as a row subquery. A row subquery is a derivation of a scalar subquery and can thus be used anywhere that a scalar subquery can be used.
-
Finally, if a subquery can return multiple columns and multiple rows, it is known as a table subquery. A table subquery is legal everywhere that a table reference is legal in an SQL statement, including the
FROM
clause of aSELECT
. It, too, is usually found in aWHERE
clause, immediately after anIN
orEXISTS
predicate or a quantified comparison operator. (A quantified comparison operator is a comparison operator used with either theSOME
,ALL
, orANY
quantifiers.)
The difference between scalar and table subqueries can be subtle. Here's a problem that arises when a subquery is written as a scalar subquery, but the subquery result contains multiple rows. Assume our two tables have only these rows:
INSERT INTO clients VALUES (10, 'sam','smith','auditor',5525.75); INSERT INTO firms VALUES (10,'abc co','leduc'),(30,'ghi inc','nisku');
Since the firms
table has two rows, this query:
SELECT * FROM clients WHERE clno < (SELECT clno FROM firms);
fails with:
"Subquery returns more than 1 row"
There are two solutions to this. The first is to change the query to include a table subquery quantified by ANY
, to compare the outer query results with any subquery value:
SELECT * FROM clients WHERE clno < ANY (SELECT clno FROM firms);
In this case, the comparison for the first client is false (10 < 10), but is true for the second client (10 < 30), and so the subquery result is "true" for clno
10. The rules for ANY
are as follows:
ANY
returns "true" if the comparison operator is "true" for at least one row returned by the subquery.ANY
returns "false" if the subquery returns zero rows or if the comparison operator is "false" for every row returned by the subquery.
SOME
is a synonym for ANY
; using IN
is equivalent to using = ANY
.
The second solution to the problem is to change the query to include a table subquery quantified by ALL
, to compare the outer query results with every subquery value:
SELECT * FROM clients WHERE clno < ALL (SELECT clno FROM firms);
In this case, the comparison is once again false for the first client and true for the second client — but this time, the subquery result is "false" and so the query returns zero rows. The rules for ALL
are:
ALL
returns "true" if the subquery returns zero rows or if the comparison operator is "true" for every row returned by the subquery.ALL
returns "false" if the comparison operator is "false" for at least one row returned by the subquery.
Does the Subquery Return at least One Row?
Sometimes, the only information needed from a subquery is whether it returns any rows at all. The [NOT] EXISTS
predicate tests for a non-empty set. EXISTS
returns "true" if the subquery returns at least one row; otherwise, it returns "false". NOT EXISTS
is the complement — it returns "true" if the subquery returns zero rows; otherwise, it returns "false". By tradition, a subquery following [NOT] EXISTS
begins with SELECT *
. In this case, the asterisk (*
) is not a shorthand for "list of all columns", instead it stands for "some column" — and the result returned by each subquery is normally correlated with the result of the outer query to which it belongs. Here's an extremely trivial example, which returns all client values:
SELECT * FROM clients WHERE EXISTS (SELECT * FROM firms);
The WHERE
clause in this example is "true" only because the firms
table is not empty. But [NOT] EXISTS
is usually used to form more complicated queries. Assume you have the following tables:
CREATE TABLE passengers ( name VARCHAR(15), compartment INT); INSERT INTO passengers VALUES ('smith',20); INSERT INTO passengers VALUES ('jones',25); CREATE TABLE cars ( compartment INT, class VARCHAR(10)); INSERT INTO cars VALUES (20,'first');
Here's an example of the classic FORALL
question:
SELECT * FROM cars c1 WHERE NOT EXISTS (SELECT * FROM passengers p1 WHERE NOT EXISTS (SELECT * FROM cars c2 WHERE c2.compartment = p1.compartment AND c2.compartment = c1.compartment));
This query is asking for the car in which all existing passengers are riding. To understand the result, consider that Smith
is in car 20
and Jones
in car 25
— but that the cars
table doesn't contain a row for car 25
. This means that there is one passenger — Jones
— who is riding in a non-existent car. (Of course, in a properly set up database, this situation couldn't exist; one would define primary key/foreign key relationships between the two tables to ensure data integrity). The second NOT EXISTS
subquery in the example, thus, is always "true" for passenger Jones.
In addition, of course, there is one passenger — Jones
, again — who is not riding in car 20
, and therefore the first NOT EXISTS
subquery in the example is "false". And since there are no other cars to check, the result of the query is an empty set (zero rows) -- there are no cars in which every passenger is riding.
Other Uses of Subqueries
The SQL Standard, effective with SQL:1999, requires increased subquery support, which MySQL provides. The row subqueries alluded to earlier are an example. Thus, it is now possible to compare multiple columns at a time:
SELECT ROW ('smith', 'auditor') = (SELECT lname, job FROM clients WHERE clno = 10);
The subquery in this example returns a row containing the values 'smith'
and 'auditor'
. When these values are compared to the ROW
values in the outer query, they are found to be equal and so the query returns 1 (true).
You can also put a subquery, rather than a simple table name, in the FROM
clause of a query (those of you familiar with Oracle will recognize this usage as an inline view):
SELECT * FROM (SELECT * FROM clients WHERE job LIKE 'a%') AS cl;
To get the result of this query, the MySQL server first evaluates the subquery and then associates the alias (cl
, in this case) with the result set. It then evaluates the outer SELECT
. In effect, the above example ends up being interpreted as:
SELECT * FROM cl;
with table cl
being a temporary result set created with the subquery:
SELECT * FROM clients WHERE job LIKE 'a%';
When a subquery is placed in the FROM
clause, the AS <alias>
portion of the syntax is mandatory; the interim result table must be named because it is referenced by the containing query.
Data Changes with Subqueries
Subqueries have one other use: they can be used to change the data in the database. That is, you can put a subquery in a DELETE
, INSERT
, UPDATE
, or REPLACE
statement. Here is an example:
UPDATE clients SET account_balance = (SELECT SUM(amount) FROM accounts where clno=clients.clno);
This UPDATE
changes the account_balance
for each client to the sum of the amounts recorded for that client in the accounts
table.
There is one caveat: It is not currently possible to modify a table and select from the same table in a subquery.
Summary
- Subqueries are new to MySQL in version 4.1, which now supports scalar, row, and table subqueries.
- The usual comparison operators —
= <> < <= > >=
— can be used with subqueries, as can the[NOT] IN
and[NOT] EXISTS
predicates. - Table subqueries can be compared using the quantifiers
ANY/SOME
orALL
. - Subqueries can be used to make data changes.
MySQL has added structure to SQL!