SQL Subqueries

What Is a Subquery?

A subquery, also known as a Nested query or Inner query, is a SELECT query within another SQL query and embedded within the WHERE or HAVING clause.

A subquery is used to return data to the outer statement to be used as a condition to filter the data to be retrieved.

Subqueries can be used with SELECT, UPDATE, INSERT, and DELETE statements together with the operators like =, <, <=, >, >=, IN, BETWEEN etc.

The subqueries are similar to the normal SELECT queries. Expect few restrictions. The most important differences are listed below:

• A subquery must always be enclosed within parentheses.
• A subquery must have only one column in the SELECT statement unless the purpose is row comparison.
• A subquery can only be a single SELECT statement. It cannot be a UNION.
• You can also use subqueries that return more than one record with multiple value operators like the IN or NOT IN operator.

Demo Table

Let us the following "Client" and "Order" tables:

• Client table:

  +----+------------+-----------+-------------+---------+
  | id | first_name | last_name | city        | country |
  +----+------------+-----------+-------------+---------+
  | 1  | Maria      | Martinez  | Boston      | USA     |
  | 2  | Donna      | Williams  | Seattle     | USA     |
  | 3  | James      | Smith     | Los Angeles | USA     |
  | 4  | Jennifer   | Garcia    | Las Vegas   | USA     |
  | 5  | Stephanie  | Miller    | Seattle     | USA     |
  | 6  | Robert     | Hernandez | Houston     | USA     |
  | 7  | Andrew     | Rodriguez | Detroit     | USA     |
  +----+------------+-----------+-------------+---------+
  

• Order table:

  +----+-----------+------------+-------------+------------+----------+
  | id | client_id | order_date | order_value | product_id | quantity |
  +----+-----------+------------+-------------+------------+----------+
  | 1  | 3         | 2018-05-26 | 114         | 26         | 4        |
  | 2  | 5         | 2018-10-01 | 63          | 15         | 2        |
  | 3  | 3         | 2018-10-06 | 29          | 19         | 1        |
  | 4  | 7         | 2018-11-12 | 160         | 33         | 3        |
  | 5  | 6         | 2018-12-16 | 260         | 7          | 2        |
  +----+-----------+------------+-------------+------------+----------+
  

Subqueries with the SELECT Statement

The subqueries are most frequently used with the SELECT statement.

Syntax

The syntax of a subquery with the SELECT statement can be given as follows:

SELECT column_name [, ... column_name_N ]
FROM table1 [ , ... table_N ]
WHERE column_name OPERATOR 
    ( SELECT column_name [ , column_name ]
    FROM table1 [ , table2 ]
    [ WHERE ] )

Example

The following SQL statement will return only those client whose ordered in quantity more than 1.

SELECT * FROM client 
WHERE id IN (SELECT DISTINCT client_id FROM order 
                WHERE quantity > 1);

The above statement will output the following result set:

+----+------------+-----------+-------------+---------+
| id | first_name | last_name | city        | country |
+----+------------+-----------+-------------+---------+
| 3  | James      | Smith     | Los Angeles | USA     |
| 5  | Stephanie  | Miller    | Seattle     | USA     |
| 6  | Robert     | Hernandez | Houston     | USA     |
| 7  | Andrew     | Rodriguez | Detroit     | USA     |
+----+------------+-----------+-------------+---------+

Subqueries with the INSERT Statement

The subqueries can also be used with the INSERT statement. The INSERT statement inserts the data returned from the subquery into another table.

Syntax

The syntax of a subquery with the INSERT statement can be given as follows:

INSERT INTO table_name [ ( column_1 [ , ... colmn_N ] ) ]
    SELECT [ * | column_1 [ , column_2 ] ]
    FROM table_1
    [ WHERE  VALUE OPERATOR ]

Example

The following SQL statement will insert the vip client's data into a table called "vip_client" by using the records returned from the subquery. The vip clients are the clients who had passed orders that quantity is superior to 2.

INSERT INTO vip_client 
SELECT * FROM client 
WHERE id IN (SELECT DISTINCT client_id FROM order 
                WHERE quantity > 2);

After executing the above command, the output will be as follows:

+----+------------+-----------+-------------+---------+
| id | first_name | last_name | city        | country |
+----+------------+-----------+-------------+---------+
| 3  | James      | Smith     | Los Angeles | USA     |
| 7  | Andrew     | Rodriguez | Detroit     | USA     |
+----+------------+-----------+-------------+---------+

Subqueries with the UPDATE Statement

The subqueries can be used with the UPDATE statement. Single or multiple columns can be updated when using a subquery with the UPDATE statement.

Syntax

The syntax of a subquery with the UPDATE statement can be given as follows:

UPDATE table_name 
SET column_name = new_value 
[ WHERE OPERATOR [ VALUE ]
    ( SELECT column_name 
        FROM table_1
            [ WHERE ]
     )
]

Example

The following SQL statement will update the order value in the "Order" table for clients who live in Seattle by increasing the current order value by 20.

UPDATE order 
SET order_value = order_value + 20
WHERE client_id IN (SELECT id FROM client 
                    WHERE city = 'Seattle');

After executing the above statement, one record in the "Order" table will be updated. The row with the id = 2 will be updated, and the value of the "order_value" will move from 63 to 83.

+----+-----------+------------+-------------+------------+----------+
| id | client_id | order_date | order_value | product_id | quantity |
+----+-----------+------------+-------------+------------+----------+
| 2  | 5         | 2018-10-01 | 63          | 15         | 2        |
+----+-----------+------------+-------------+------------+----------+

Subqueries with the DELETE Statement

The subqueries can be used with the DELETE statement to delete a single or multiple rows in a table.

Syntax

The syntax of a subquery with the DELETE statement can be given as follows:

DELETE FROM table_name 
[ WHERE OPERATOR [ VALUE ]
    ( SELECT column_name 
      FROM  table_1 
     [ WHERE ]
     )
]

Example

The following SQL statement will delete rows from the "Order" table that were passed by clients who live in Seattle.

DELETE FROM order 
WHERE client_id IN (SELECT id FROM client 
                    WHERE city = 'Seattle');