Table of contents

Introduction

What are the Sub Queries?

Rules to use Subqueries in SQL

3.1.

Sample Table

3.2.

Subqueries within SELECT statement

3.3.

Subqueries within INSERT Statement

3.4.

Subqueries within UPDATE Statement

3.5.

Subqueries within DELETE Statement

Frequently asked questions

Key takeaways

Last Updated: Mar 27, 2024

Sub Queries

Author Alisha chhabra

Do you think IIT Guwahati certified course can help you in your career?

Yes

Introduction

You must have often heard of Queries while learning the DBMS concepts. To recap the Query, it is nothing but a question or inquiry about a set of data. To retrieve meaningful and relevant data, Queries are employed. You must be wondering why we are discussing Queries? 🤔

Well, that's the prerequisite before proceeding with today's discussion. As the name implies, a Subquery is a query within a query or during programming you've probably encountered with the Nested Loops. Likewise, subqueries can also be known as the Nested Queries. Seems interesting, right?

In this article, we'll be looking at what exactly Subqueries entail, along with several examples that can make you become a master Subquery maker.

Recommended topics, Coalesce in SQL and Tcl Commands in SQL

Let us get started now:

What are the Sub Queries?

Sub Queries refers to a query that is embedded within another SQL query. Subqueries are a practical tool for carrying out operations in several phases.

Furthermore, the execution occurs in the following order: A subquery is executed first and returns the result used by the main Query depending on the condition to further restrict the data retrieval. Henceforth, the subquery is referred to as the Inner Query, while the primary Query is the Outer Query.

Syntax:

The one thing necessary while writing the subquery is to enclose it with parentheses().

The following syntax entails how to write a subquery:

SELECT column_name(s)
FROM table_name(s)
WHERE column_name OPERATOR (SELECT column_name(s)
FROM table_name(s)
[WHERE]);

There can be different syntaxes based on the requirement. As discussed above, the inner Query is executed; first, its result is compared with the outer Query. Let us see some use cases for better understanding:

Subqueries are compatible with practically all SQL commands, for example,

INSERT
UPDATE
SELECT
DELETE

Before we discuss the examples, let us understand why we use Subqueries?

They are used to execute a query dependent on the outcome of another query. Subqueries allow you to accomplish this without writing two distinct queries and copy-pasting the results.

Rules to use Subqueries in SQL

When developing SQL Subqueries, we must adhere to specific guidelines. We'll go over the regulations in more detail below:

Subqueries, which can be used with Insert, Update, Delete, and Select statements, must be included in the Where clause.
We can utilise comparison operators for subqueries such as: >, > =, =,!=, IN, Between.
The subquery is always run first, followed by the main Query.
Parentheses should be used to surround subqueries.
Subqueries always appear to the right of the comparison operators.
In the subquery, we cannot use the Order By clause; instead, we can use the Group By clause.
We cannot use the Between clause with a subquery, but we may use the Between clause within a subquery.

Sample Table

The table given below is the reference table throughout the article:

Let us now discuss the examples:

Subqueries within SELECT statement

Subqueries are often used with the SELECT statement to retrieve the relevant data from the table.

Let us discuss some queries based on the above table:

Example-1:

Write a SQL Query to display the employee name from the Employee table whose Salary is maximum.

Before writing a query, it is recommended to break down the query statement into possible parts. Here, we have to find the employee name whose Salary is maximum. To find the employee, we must have the maximum Salary.

To find the maximum Salary, we can use the max() aggregate function as shown below:

SELECT max(salary) from Employee;

Now, if we try to run the above Query, the output would be:

60,000

To further fetch the employee name whose SalarySalary is 60,000, the below Query can be applied:

SELECT empName from Employee
WHERE Salary = (SELECT max(salary) from Employee); -- subquery

Output:

Kendall

Example-2:

Write a SQL Query to display the employee's details having the Salary greater or equal to 45,000.

Let us decompose this Query into two sections:

To find the Salary greater or equal to the 45,000:

SELECT Salary from Employee
WHERE Salary >= 45000;

The above Query will return the following output:

Let us now use the above Query as a subquery to find the details of the employees.

SELECT * from Employee
WHERE Salary IN (SELECT Salary from Employee
WHERE Salary >= 45000);

Example-3:

Write a SQL Query to display the Employee name, taking the second-highest Salary.

Let us decompose the given Query:

To find the Employee's name who is taking the second-highest Salary, we must find the second highest Salary.
To find the second highest Salary, we must find the highest Salary, which can be done with the max() aggregate function. But to find the second highest Salary, we need to exclude the highest Salary.

The following Query returns the maximum Salary from the table EMPLOYEE.

SELECT max(Salary) from Employee; -- 60,000

The following Query excludes the highest Salary using the <>(NOT EQUAL TO) operator.

SELECT max(Salary) from Employee
WHERE Salary <> (SELECT max(Salary) from Employee); -- 50,000

With the help of the above queries we can find the Employee who is taking the second-highest Salary:

SELECT EmpName from Employee
WHERE Salary IN (SELECT max(Salary) from Employee
WHERE Salary <> (SELECT max(Salary) from Employee));

Output:

Example 4:

Write a SQL query to display the Employees' names working in the department where no. of employees are less than 2.

To find the number of employees working in which department, we can group the departments with the help of the GROUP BY clause, and by using the COUNT() aggregate function, we can find the number of employees. We can also find the dept names having employees less than 2.

SELECT Dept from Employee
GROUP BY Dept HAVING Count(*)<2; -- HR, Marketing

Using the above Query as a subquery, we can find the employee's name:

SELECT EmpName from Employee
WHERE Dept IN (SELECT Dept from Employee
GROUP BY Dept HAVING Count(*)<2);

Output:

Let us now have a look at some other SQL commands:

Subqueries within INSERT Statement

The INSERT statement utilises the returned data from the subquery to INSERT into another table.

The following syntax depicts the use of Subqueries within Insert statement:

INSERT INTO tableName
SELECT * or column[s] from tableName[s]
[ WHERE VALUE OPERATOR]

Example 1:

Write a SQL query to insert the data from the Employees table into the EmployeeAlt table.

Assume there is a table EmployeeAlt in the database.

The following Query can be used to accomplish the above task:

INSERT INTO EmployeeAlt
SELECT * from Employee
WHERE EmpID IN ( SELECT EmpID from Employee);

Output:

Example 2:

Write a SQL query to insert only employees with ages less than or equal to 26 into the EmployeeAlt table.

The following Query can be used to accomplish the above task:

INSERT INTO EmployeeAlt
SELECT * FROM Employee
WHERE Age IN ( SELECT Age from Employee WHERE Age<=26);

Output:

Subqueries within UPDATE Statement

For changing single or several columns in a table, utilise the subquery in combination with the UPDATE command.

The following syntax is used to execute a subquery within UPDATE Statement:

UPDATE tableName
SET column_name = new_value
WHERE OPERATOR
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]

Example:

Write a SQL query to update the Salary by 0.75 times in the Employees table whose Age is greater than 25 from the EmployeeAlt table.

Query:

UPDATE Employee
SET Salary = Salary * 0.75
WHERE Age IN ( SELECT Age from EmployeeAlt WHERE Age > 25);

Output:

Subqueries within DELETE Statement

The subquery can also be used with the DELETE statement to delete one or multiple records from the table.

Syntax:

DELETE FROM TableName
WHERE OPERATOR
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]

Example:

Write a SQL query to delete the records from the Employee table whose Country is 'Mexico’ from the EmployeeAlt table.

DELETE FROM Employee
WHERE Country NOT IN ( SELECT Country from EmployeeAlt WHERE Country = ''Mexico'"');

Output:

Frequently asked questions

In SQL, how many subqueries can be written?

There is a maximum of 32 layers of nesting, which varies depending on available memory and the complexity of other expressions in the Query.

Can group by and order by clauses be used in subqueries?

THE GROUP BY clause can be used in subqueries, but the ORDER BY clause cannot be used.

What are the drawbacks of using Subqueries?
- We cannot modify and select from the same table within a subquery in the same SQL statement.
- Since the optimizer for MYSQL is more robust for joins than for subqueries, rewriting a statement that utilises a subquery can often result in a less efficient execution.

Key takeaways

To wrap up the session, We learned what subqueries are, how to create them using various SQL commands, and what crucial considerations to remember while conducting SQL subqueries. In the context of interviews, subqueries are often asked. It is considered one of the hot topics in SQL.