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.
Stay updated with latest technology trends
Join DataFlair on Telegram!!
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,
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).
Let us have a look at our demo database.
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.
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.
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.
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.
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.