SQL Sub Queries For Beginners [Nested SQL Queries]

Introduction

In this article we talk about sub-queries in relational databases.

And how to use them with examples.

What Is A Subquery?

A subquery is a SQL query nested inside a larger query.

Where can you use subqueries?

A subquery can be used in:

• A SELECT clause
• A FROM clause
• A WHERE clause

Multiple Levels Of SQL Query Nesting

A subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement.

Or inside another subquery.

Typical Sub Query Usage

A subquery is usually added within the WHERE Clause of another SQL SELECT statement.

Subquery Order Of Execution

The inner query executes first before its parent query.

The results of an inner query can be passed to the outer query.

Subqueries Aren't Just for Selects

You can use a subquery in:

• SELECT
• INSERT
• DELETE
• UPDATE

Example Subquery use Cases

Subqueries let you perform the following tasks:

• Complex querying and filtering.

• Compare an expression to the result of the query.

• Determine if an expression is included in the results of the query.

• Check whether the query selects any rows.

Subqueries with the SELECT Statement:

Consider the CUSTOMERS table having the following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Chris    |  35 | Paris     |  2000.00 |
|  2 | Khaleesi |  25 | London    |  1500.00 |
|  3 | Will     |  23 | Medellin  |  2000.00 |
|  4 | Grace    |  25 | Cancun    |  6500.00 |
|  5 | Mark     |  27 | Ibiza     |  8500.00 |
|  6 | Brian    |  22 | Sydney    |  4500.00 |
|  7 | Matt     |  24 | Tokyo| 10000.00 |
+----+----------+-----+-----------+----------+

Now, let us check the following subquery with a SELECT statement.

Example:

SELECT *
FROM CUSTOMERS
WHERE ID IN (
    SELECT ID
    FROM CUSTOMERS
    WHERE SALARY > 4500
);

This would produce the following result.

+----+----------+-----+---------+----------+
| ID | NAME     | AGE | ADDRESS | SALARY   |
+----+----------+-----+---------+----------+
|  4 | Grace    |  25 | Cancun  |  6500.00 |
|  5 | Mark     |  27 | Ibiza   |  8500.00 |
|  7 | Matt     |  24 | Tokyo   | 10000.00 |
+----+----------+-----+---------+----------+

Subqueries with the UPDATE Statement

The subquery can be used in conjunction with the UPDATE statement.

Either single or multiple columns in a table can be updated when using a subquery with the UPDATE statement.

Update Example

Assuming, we have a CUSTOMERS_BKP table available which is a backup of the CUSTOMERS table.

The following example updates SALARY by 0.25 times in the CUSTOMERS table.

For all the customers whose AGE is greater than or equal to 27.

UPDATE CUSTOMERS
SET SALARY = SALARY * 0.25
WHERE AGE IN (
    SELECT AGE
    FROM CUSTOMERS_BKP
    WHERE AGE >= 27
);

This would impact two rows and the CUSTOMERS table would have the following records.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Chris    |  35 | Paris     |   125.00 |
|  2 | Khaleesi |  25 | London    |  1500.00 |
|  3 | Will     |  23 | Medellin  |  2000.00 |
|  4 | Grace    |  25 | Cancun    |  6500.00 |
|  5 | Mark     |  27 | Ibiza     |  2125.00 |
|  6 | Brian    |  22 | Sydney    |  4500.00 |
|  7 | Matt     |  24 | Tokyo     | 10000.00 |
+----+----------+-----+-----------+----------+

Subqueries with the DELETE Statement_**:

The subquery can be used in conjunction with the DELETE statement like with any other statements mentioned above.

Example:

Assuming, we have a CUSTOMERS_BKP table available which is a backup of the CUSTOMERS table.

The following example deletes the records from the CUSTOMERS table.

for all the customers whose AGE is greater than or equal to 27.

DELETE FROM CUSTOMERS
WHERE AGE IN (
    SELECT AGE
    FROM CUSTOMERS_BKP
    WHERE AGE >= 27
);

This would impact two rows and finally the CUSTOMERS table would have the following records.

+----+----------+-----+---------+----------+
| ID | NAME     | AGE | ADDRESS | SALARY   |
+----+----------+-----+---------+----------+
|  2 | Khaleesi |  25 | London  |  1500.00 |
|  3 | Will     |  23 | Medellin|  2000.00 |
|  4 | Grace    |  25 | Cancun  |  6500.00 |
|  6 | Brian    |  22 | Sydney  |  4500.00 |
|  7 | Matt     |  24 | Tokyo   | 10000.00 |
+----+----------+-----+---------+----------+

Conclusion: SQL Subqueries

Subqueries are nested queries in SQL.

They can be used for advanced reporting and complex filtering.

Today, we talked at a high level about what Sql Queries are and how to use them.

Get SQL Query Help From Me

It will be my pleasure to help you with your SQL Query situation.

Message me Today and tell me what you have going on!