Select Statement  «Prev  Next»

Lesson 8Subquery statements using EQUALS clause
Objective EQUALS clause and how it works as subquery statement.

Using EQUALS clause with Subquery

Earlier, we mentioned there are two different approaches to SELECT and subquery statements.
  1. The first approach is the IN clause, which we have already covered.
  2. The second is the EQUALS clause, indicated quite simply with the = sign.
The syntax for the two clauses is nearly identical, with the = sign substituted for the IN phrase:

Example of Subquery

SELECT Title FROM Titles
WHERE pub_id=
(SELECT Pub_ID FROM Publishers 
WHERE State='CA')

There is one requirement if you use this approach. Make sure the subquery statement returns only one value.
With the IN clause, you returned a list of values used as a comparison. With the EQUALS approach, there can be one and only one value represented when the results of the subquery are evaluated.
The subselect statement can have nearly any additional clauses that you can put on a standard SELECT statement.
You can use WHERE, or you can use the keywords that you are familiar with from your experience with SQL.

In the next lesson, the details about using the DISTINCT keyword and how you can integrate it into your SELECT (and sub-SELECT) statements will be discussed.

What is a subquery?

A subquery is a query contained within another SQL statement (which I refer to as the containing statement for the rest of this discussion). A subquery is always enclosed within parentheses, and it is usually executed prior to the containing statement. Like any query, a subquery returns a result set that may consist of:
  1. A single row with a single column
  2. Multiple rows with a single column
  3. Multiple rows and columns
The type of result set the subquery returns determines how it may be used and which operators the containing statement may use to interact with the data the subquery returns. When the containing statement has finished executing, the data returned by any subqueries is discarded, making a subquery act like a temporary table with statement scope (meaning that the server frees up any memory allocated to the subquery results after the SQL statement has finished execution). You already saw several examples of subqueries in earlier chapters, but here's a simple example to get started: 

mysql> SELECT MAX(account_id) FROM account;
+-----------------+
| MAX(account_id) |
+-----------------+
|       29        |
+-----------------+
1 row in set (0.00 sec)
In this example, the subquery returns the maximum value found in the account_id column in the account table, and the containing statement then returns data about that account. If you are ever confused about what a subquery is doing, you can run the subquery by itself (without the parentheses) to see what it returns.
Here is the subquery from the previous example: 
mysql> SELECT MAX(account_id) FROM account;
+-----------------+
| MAX(account_id) |
+-----------------+
|         29      |
+-----------------+
1 row in set (0.00 sec)

The subquery returns a single row with a single column, which allows it to be used as one of the expressions in an equality condition (if the subquery returned two or more rows, it could be compared to something but could not be equal to anything). In this case, you can take the value the subquery returned and substitute it into the righthand expression of the filter condition in the containing query, as in: 
mysql > SELECT account_id, product_cd, cust_id, avail_balance
-> FROM account
-> WHERE account_id = 29;
+------------+------------+---------+---------------+
| account_id | product_cd | cust_id | avail_balance |
+------------+------------+---------+---------------+
|      29    |     SBL    |     13  |    50000.00   |
+------------+------------+---------+---------------+
1 row in set (0.02 sec)