MySQL subqueries

In this part of the MySQL tutorial, we will mention subqueries in MySQL.

A subquery is a query in a query. It is also called an inner query or a nested query. A subquery can be used anywhere an expression is allowed. It is a query expression enclosed in parentheses. Subqueries can be used with SELECT, INSERT, UPDATE, or DELETE statements.

There is more than one way to execute an SQL task. Many subqueries can be replaced by SQL joins. SQL joins are usually faster.

In this chapter, we will be using the following tables:

mysql> SELECT * FROM Cars;
+----+------------+--------+
| Id | Name       | Cost   |
+----+------------+--------+
|  1 | Audi       |  52642 |
|  2 | Mercedes   |  57127 |
|  3 | Skoda      |   9000 |
|  4 | Volvo      |  29000 |
|  5 | Bentley    | 350000 |
|  6 | Citroen    |  21000 |
|  7 | Hummer     |  41400 |
|  8 | Volkswagen |  21600 |
+----+------------+--------+

The data from the Cars table.

mysql> SELECT * FROM Customers; SELECT * FROM Reservations;
+------------+-------------+
| CustomerId | Name        |
+------------+-------------+
|          1 | Paul Novak  |
|          2 | Terry Neils |
|          3 | Jack Fonda  |
|          4 | Tom Willis  |
+------------+-------------+
4 rows in set (0.00 sec)

+----+------------+------------+
| Id | CustomerId | Day        |
+----+------------+------------+
|  1 |          1 | 2009-11-22 |
|  2 |          2 | 2009-11-28 |
|  3 |          2 | 2009-11-29 |
|  4 |          1 | 2009-11-29 |
|  5 |          3 | 2009-12-02 |
+----+------------+------------+
5 rows in set (0.00 sec)

We recapitulate what we have in the Customers and Reservations tables. Subqueries are often performed on tables, which have some relationship.

Subquery with the INSERT statement

We want to create a copy of the Cars table. Into another table called Cars2. We will create a subquery for this.

mysql> CREATE TABLE Cars2(Id INT NOT NULL PRIMARY KEY, 
    -> Name VARCHAR(50) NOT NULL, Cost INT NOT NULL);

We create a new Cars2 table with the same columns and datatypes as the Cars table. To find out how a table was created, we can use the SHOW CREATE TABLE statement.

mysql> INSERT INTO Cars2 SELECT * FROM Cars;

This is a simple subquery. We insert all rows from the Cars table into the Cars2 table.

mysql> SELECT * FROM Cars2;
+----+------------+--------+
| Id | Name       | Cost   |
+----+------------+--------+
|  1 | Audi       |  52642 |
|  2 | Mercedes   |  57127 |
|  3 | Skoda      |   9000 |
|  4 | Volvo      |  29000 |
|  5 | Bentley    | 350000 |
|  6 | Citroen    |  21000 |
|  7 | Hummer     |  41400 |
|  8 | Volkswagen |  21600 |
+----+------------+--------+

The data was copied to a new Cars2 table.

Scalar subqueries

A scalar subquery returns a single value.

mysql> SELECT Name FROM Customers WHERE 
    -> CustomerId=(SELECT CustomerId FROM Reservations WHERE Id=5);
+------------+
| Name       |
+------------+
| Jack Fonda |
+------------+

The query enclosed in parentheses is the subquery. It returns one single scalar value. The returned value is then used in the outer query. In this scalar subquery, we return the name of the customer from the Customers table, whose reservation has Id equal to 5 in the Reservations table.

Table subqueries

A table subquery returns a result table of zero or more rows.

mysql> SELECT Name FROM Customers WHERE CustomerId IN    
    -> (SELECT DISTINCT CustomerId FROM Reservations);
+-------------+
| Name        |
+-------------+
| Paul Novak  |
| Terry Neils |
| Jack Fonda  |
+-------------+

The above query returns the names of the customers, who made some reservations. The inner query returns customer Ids from the Reservations table. We use the IN predicate to select those names of customers, who have their CustomerId returned from the inner select query.

mysql> SELECT DISTINCT Name FROM Customers JOIN Reservations
    -> ON Customers.CustomerId=Reservations.CustomerId;
+-------------+
| Name        |
+-------------+
| Paul Novak  |
| Terry Neils |
| Jack Fonda  |
+-------------+

The previous subquery can be rewritten using SQL join.

Correlated subqueries

A correlated subquery is a subquery that uses values from the outer query in its WHERE clause. The subquery is evaluated once for each row processed by the outer query.

mysql> SELECT Name FROM Cars WHERE Cost <
    -> (SELECT AVG(Cost) FROM Cars);
+------------+
| Name       |
+------------+
| Audi       |
| Mercedes   |
| Skoda      |
| Volvo      |
| Citroen    |
| Hummer     |
| Volkswagen |
+------------+

In the above correlated subquery, we return all cars that cost below the average price of all cars in the table.

Subqueries with EXISTS, NOT EXISTS

If a subquery returns any values, then the predicate EXISTS returns TRUE, and NOT EXISTS FALSE.

mysql> SELECT Name FROM Customers WHERE EXISTS
    -> (SELECT * FROM Reservations WHERE
    -> Customers.CustomerId=Reservations.CustomerId);
+-------------+
| Name        |
+-------------+
| Paul Novak  |
| Terry Neils |
| Jack Fonda  |
+-------------+

In the above SQL statement we select all customers' names, which have an entry in the Reservationstable.

mysql> SELECT Name FROM Customers WHERE NOT EXISTS    
    -> (SELECT * FROM Reservations WHERE 
    -> Customers.CustomerId=Reservations.CustomerId);
+------------+
| Name       |
+------------+
| Tom Willis |
+------------+

In this query, we return all customers that do not have an entry in the Reservations table. Both SQL queries are correlated queries.