SQL Subquiries

A subquery (an inner or nested query) is a query within another SQL query (a main or outer query). Subqueries can be nested within SELECT, UPDATE, INSERT, and DELETE statements.

Subqueries in SELECT statement

Subqueries nested in SELECT statements are the most common type of subqueries. A subquery may occur in the SELECT clause, in the FROM clause, and in the WHERE clause of the SELECT statement.

Example

Table registered_users has two columns: username and sign_up_date :

Use a query with a subquery nested into the WHERE clause to select the first registered users:

SELECT
    username,
    sign_up_date 
FROM
    registered_users 
WHERE
    sign_up_date = (
        SELECT 
            MIN(sign_up_date) 
        FROM 
            registered_users
    );

The result of the query will look as follows:

The subquery from the example above is a single-row subquery. In this case, it is safe to use simple SQL comparison operators (=, >, <=, etc.) because we can be sure that our query will return only one row with one column.

Using a subquery along with comparison operators and the result subquery returns more than one row, there will be an error. This doesn't mean you can't use the multiple-row subquery that will return one or more rows. Use a multiple-row comparison operator, such as IN and ANY.

Use a subquery to select one of the columns in the SELECT statement to add new columns to the result set.

Let's select all data from our table registered_users and add some more info from the table users_info given below:

To select all the information from the registered_users table and add all the corresponding information about the actual usernames from the users_info table:

SELECT *, 
    (SELECT 
         name 
     FROM 
         users_info 
     WHERE 
         username = registered_users.username) AS name 
FROM
    registered_users

The result will look as follows:

As was already mentioned, use a subquery in the FROM clause of the SELECT statement. In this case, a derived table (a table we obtain with subquery) must be aliased.

The subqueries can be correlated. Correlated subqueries use values from the outer query.

Take a look at the example of a correlated subquery. Here is the table new_orders with the columns id INT, product VARCHAR(40), product_category VARCHAR(40), quantity INT and unit_price INT:

Use a correlated subquery to select all the products with a unit price less than the average in the product category:

SELECT 
    id,
    product 
FROM
    new_orders AS newor 
WHERE unit_price < (
    SELECT 
        AVG(unit_price) 
    FROM 
        new_orders
    WHERE 
        product_category = newor.product_category);

While query results are evaluated, the correlated subquery will be executed for each row in the table, so evaluating the query with subquery for large tables can take a lot of time.

Subqueries nested in the UPDATE statement

The subqueries can also be used with the UPDATE statement.

Let's update the table students with the columns name VARCHAR(40), scholarship INT, and exams_passed BOOLEAN:

As with the SELECT, use the UPDATE statement with a subquery nested into the WHERE clause. Use this type of query with a subquery to set exams_passed to TRUE if in the exam_results table, both exam marks are greater or equal to 18. The table exam_results looks as follows (name VARCHAR(40), math_exam_mark INT, and english_exam_mark INT):

To update the table students use this query:

UPDATE
    students 
SET
    exams_passed = TRUE 
WHERE 
    name IN (
        SELECT
            name
        FROM
            exam_results 
        WHERE
            math_exam_mark >= 18
            AND english_exam_mark >= 18
        ); 

After the execution of the query above, the table students will look as follows:

Use the UPDATE query with a subquery to set the modified value. Use the query below to set scholarship column values equal to the minimal scholarship for all students who did not pass the exams. Suppose that the scholarship amounts are stored in the scholarships table.

UPDATE
    students 
SET
    scholarship = (
        SELECT 
            MIN(amount)
        FROM scholarships
    ) 
WHERE
    exam_passed = FALSE;

Now Tamara Fetch will have a scholarship equal to 200:

Subqueries nested in the INSERT statement

In the INSERT statement uses a subquery to insert the data returned from the subquery into another table.

In the example below, let's work with the table employees with the columns name VARCHAR(20), salary INT and department_id INT:

All the data about departments is stored in the table departments with the columns id INT and department VARCHAR(20):

Let's add to the employees table the info about Tomas Hedwig, who works in the PR department and has a salary equal to Ann Reed's. To get the salary and department IDs, use subqueries as shown below:

INSERT INTO employees 
VALUES (
    'Tomas Hedwig', 
    (SELECT salary FROM employees WHERE name = 'Ann Reed'), 
    (SELECT id FROM departments WHERE department = 'PR')
)

After the query execution the employees table will have two rows:

Subqueries nested in the DELETE statement

Use subqueries in the DELETE statement as a part of the condition in the WHERE clause.

Assume there's a table orders with columns order_id INT, customer_id INT, product VARCHAR(20), and city VARCHAR(20):

And another table customers with columns customer_id INT and name VARCHAR(40):

To delete from the table orders all the orders made by Ann Smith, the query will be:

DELETE FROM orders
WHERE customer_id = (SELECT customer_id FROM customers WHERE name = 'Ann Smith'

After the query execution, the orders table will look as follows:

Same as with the UPDATE, you cannot use the same table in the query and subquery in the DELETE statement.