Learning SQL - Subqueries

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).

Like any query, a subquery returns a result set that may consist of: • A single row with a single column • Multiple rows with a single column • Multiple rows having multiple columns

Subquery Types

Noncorrelated Subqueries

Subqueries are completely self-contained, it may be executed alone and does not reference anything from the containing statement.

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. This type of subquery is known as a scalar subquery and can appear on either side of a condition using the usual operators (=, <>, <, >, ⇐, >=).

Multiple-Row, Single-Column Subqueries If you use a subquery in an equality condition but the subquery returns more than one row, you will receive an error. However, there are four additional operators that you can use to build conditions with these types of subqueries.

The in and not in operators The all operator The any operator

Multicolumn Subqueries

To show the utility of multicolumn subqueries, it might help to look first at an example that uses multiple, single-column subqueries

mysql> SELECT fa.actor_id, fa.film_id 
	-> FROM film_actor fa
	-> WHERE fa.actor_id IN 
	-> (SELECT actor_id FROM actor WHERE last_name = 'MONROE') 
	-> AND fa.film_id IN 
	-> (SELECT film_id FROM film WHERE rating = 'PG');
	
	| actor_id | film_id |
	| 120 | 63 | 
	| 120 | 144 | 
	| 120 | 414 | 
	| 120 | 590 | 
	| 120 | 715 | 
	| 120 | 894 | 
	| 178 | 164 | 

This query uses two subqueries to identify all actors with the last name Monroe and all films rated PG, and the containing query then uses this information to retrieve all cases where an actor named Monroe appeared in a PG film. However, you could merge the two single-column subqueries into one multicolumn subquery and com‐ pare the results to two columns in the film_actor table. To do so, your filter condi‐ tion must name both columns from the film_actor table surrounded by parentheses and in the same order as returned by the subquery, as in:

mysql> SELECT actor_id, film_id 
-> FROM film_actor 
-> WHERE (actor_id, film_id) IN 
-> (SELECT a.actor_id, f.film_id 
	-> FROM actor a 
	-> CROSS JOIN film f 
	-> WHERE a.last_name = 'MONROE'
	-> AND f.rating = 'PG');)

Correlated Subqueries

A correlated subquery, is dependent on its containing statement from which it references one or more columns, meaning that you can’t execute them by themselves and inspect the results. The correlated subquery is executed once for each candidate row

mysql> SELECT c.first_name, c.last_name 
-> FROM customer c 
-> WHERE 20 = 
-> (SELECT count(*) FROM rental r 
	-> WHERE r.customer_id = c.customer_id);

The reference to c.customer_id at the very end of the subquery is what makes the subquery correlated; the containing query must supply values for c.customer_id for the subquery to execute.

One word of caution: since the correlated subquery will be executed once for each row of the containing query, the use of correlated subqueries can cause performance issues if the containing query returns a large number of rows.

The exists Operator

mysql> SELECT c.first_name, c.last_name 
-> FROM customer c 
-> WHERE EXISTS 
-> (SELECT 1 FROM rental r 
	-> WHERE r.customer_id = c.customer_id 
	-> AND date(r.rental_date) < '2005-05-25');

Using the exists operator, your subquery can return zero, one, or many rows, and the condition simply checks whether the subquery returned one or more rows. If you look at the select clause of the subquery, you will see that it consists of a single literal (1); since the condition in the containing query only needs to know how many rows have been returned, the actual data the subquery returned is irrelevant.

When to Use Subqueries