Subqueries

Typically, when working with data, we need to use the results of one query execution in the second query. For example, we need to select the tuples where the value from one column is equal to the max value in another column. We can do it with simple subqueries: select the max value and in the main query select the tuples which are equal to this value. Thankfully, SQL has a powerful tool to combine queries — subqueries. Let's have a look at it!

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

Subqueries nested 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.

Let's have a look at the following example.

Table registered_users has two columns: username and sign_up_date :

We will 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 (=, >, <=, e.t.c.), because we can be sure that our query will return only one row with one column.

If we use a subquery along with comparison operators and our subquery returns more than one row, we will get an error. This doesn't mean that you can't use the multiple-row subquery that will return one or more rows, just use a multiple-row comparison operator, such as IN and ANY.

We can 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:

We will select all the information from the registered_users table and add all the corresponding information about the actual user names 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 we've already mentioned, you can also use a subquery in the FROM clause of the SELECT statement. In this case, a derived table (a table that we obtain with subquery) has to be aliased.

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

Let's have 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:

We will 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, we can use the UPDATE statement with a subquery nested into the WHERE clause. We will use this type of query with a subquery to set exams_passed to TRUE if in the exam_results table both exams 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 we can 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, our table students will look as follows:

We can also use the UPDATE query with a subquery to set the modified value. We will 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, we can use a subquery to insert the data returned from the subquery into another table.

In the example below we will 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 with a salary equal to Ann Reed's salary. To get the salary and department IDs we will 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

We can also use subqueries in the DELETE statement as a part of the condition in the WHERE clause.

Assume that we have the 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):

We will delete from the table orders all the orders made by Ann Smith:

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.

Conclusion

In this topic, we've learned about the different types of subqueries. Now you know that we can use subqueries in the different parts of the query and with the different types of statements. Let's get to practice then!