SQL Subquery – Types of Subqueries in SQL

In our previous tutorials, we have seen how to create, update, and delete databases with various queries. In this tutorial, we will dive into the world of nested queries.

What are SQL Subqueries?

SQL subqueries or nested queries are SQL statements where we need the results from our database after using multiple filters.

A subquery is put to restrict the data pool for the main query i.e., the inner query gives us the data which is the pool for the main query.

Subqueries are compatible with almost all SQL statements, for example,

• INSERT
• UPDATE
• DELETE
• SELECT

We embed the inner query in the Where clause to get the required results.

The syntax for SQL subqueries are as follows:

SELECT column_name
 FROM table_name1
WHERE VALUE IN 
(SELECT column_name
FROM table_name2 
WHERE condition)

Rules to Use Subqueries in SQL

We need to follow some rules while writing SQL Subqueries. We will discuss the rules below:

• Subqueries need to be enclosed in the Where clause and can be used with Insert, Update, Delete, and Select statements.
• We can use comparison operators for example: <, >, > =, < =, !=, IN , Between for the subqueries.
• The subquery is always executed first and then the main query.
• Subquery should be enclosed within parentheses.
• Subqueries are always to the right of the comparison operators.
• We can’t use Order By clause in the subquery; instead, we can use the Group By clause.
• We should use single-row operators with single-row subqueries and vice versa.
• We can’t use Between clause with a subquery, but we can use Between in a subquery.

Types of SQL Subqueries

We have various subqueries; some of them are as follows:

1. Single Row Subquery

Returns zero or one row in results.

2. Multiple Row Subquery

Returns one or more rows in results.

3. Multiple Column Subqueries

Returns one or more columns

4. Correlated Subqueries

Returns one or more columns according to the main or the outer query, thus called a correlated subquery.

5. Nested Subqueries

We have queries within a query(inner and outer query).

Demo Database

Let us have a look at our demo database.

Query: 

SELECT * FROM DataFlair_emp1;

We can view our demo database.

SELECT Statement with Subquery

Example 1: Let us find the second highest salary of the employee in DataFlair.
Query: 

SELECT emp_id, MAX(salary) AS salary
  FROM dataflair_emp1
 WHERE salary < (SELECT MAX(salary)
                 FROM dataflair_emp1); 

Here we can see the second-largest salary of the employee in DataFlair.

Update Statement with Subquery

Example 2: Let us increase the salary of Senior Manager to 35000.
Query:

UPDATE dataflair_emp1 
SET salary=35000
WHERE emp_id  = ( SELECT emp_id
                    FROM dataflair_emp1
                    WHERE post='Sr.Manager');     
select * from dataflair_emp1;

Delete Statement with Subquery

Example 3: Let us delete the employee with the age = 25.
Query: 

DELETE FROM dataflair_emp1
WHERE emp_id IN ( SELECT emp_id
                   FROM dataflair_emp2
                   WHERE age=25);  
select * from dataflair_emp1;

Here we can see the records corresponding to the result of subquery are dropped.

Summary

In this tutorial, we got the essence of what subqueries are and how we use them. We have rules and theories which we need to follow while writing a subquery.

The major takeaway is that a subquery executes from the inner query to the outer query. When we use subqueries, we are able to obtain the answers to very complex analytics.

Subqueries make the operations easy and more readable for the user. Subqueries are the major tool of a DBA, and one should have a good grip on subqueries to become a DBA.