Subquery in SQL is Execution or writing of two different select statement in single SQL Query is known as subquery.
what is subquery?
SQL Subquery is Query written inside the select query that can be known as subquery. Execution or writing of two different select statement in single SQL Query is known as subquery.
Subquery is defined as SQL select Query within select query, WE can say that nested select query is called as subquery.
Table of Contents
Consider the example we have a database EMPDB with two different tables EmpMaster and Employee
and we have to find the employees which are not in Employee table and are present in the EmpMaster table for this scenario we can write Subquery to fetch the data.
Select Column_X,COlumn_y,COlumn_Z from Table_one
where Column_X not in (Select COlumn X from Table_two where Condition) The Outer Query Retrieves will receive data all data from table_one which is not in table_two
Inner Subquery select coulmn_x from table_two depending upon defined condition.
Outer Query’s where condition will compare the records from table_one and table_two.
Subquery in SQL Example
Subqueries in SELECT Statement
select ID,Name,Email,Salary,Mobile from EmpMaster a
where ID not in
(Select EmployeeID from Employee where EmployeeCity='Mumbai');Here, Outer query EmpMaster table will fetch the data for ID,Name,Email,Salary,Mobile columns where ID is not In the Employee table.
Inner Subquery will fetch the records from the employee table who’s EmployeeCity is Mumbai
Empmaster will return only those records which are matched with subquery result.
Subquery in sql Example 2
Suppose you have a EmpMaster table, and you need an all-employee detail with their city, but EmployeeCity is not in EmpMaster table then you can use Subquery to select Only City column from another table Employee.
select ID,Name,Email,Salary,Mobile,
(Select EmployeeCity from Employee where a.ID=EmployeeID)Emp_city
from EmpMaster aIn Above SQL query EmployeeCity Column will be fetched from Employee table matching with ID from EmpMaster table and EmployeeId from Employee table.
here Emp_City column result is Subquery and Empmaster table selection is Outer query or Main Query.
How to fix Subquery returned more than 1 value
select ID,Name,Email,Salary,Mobile,
(Select EmployeeCity from Employee )Emp_city
from EmpMaster aWhen using Subquery make sure to match all records present in the Outer query should be matched with Inner or Subquery, if you have maximum records in your subquery and not matching with your outer query then you will get error like below.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
To Resolve Subquery returned more than 1 value issue you need to check duplicate records from subquery or you should have same records in your outer query and subquery for that you can join your subquery with your main query or outer query.
Select ID,Name,Email,Salary,Mobile,
(Select EmployeeCity from Employee where a.ID=EmployeeID)Emp_city
from EmpMaster aSubqueries can be used for Below Operators LIKE , IN ,NOT IN ,=,> ,< >=,<= ,EXISTS etc.
Subqueries can be used to Update and Delete: you can update the records from the table or delete the data from table defined as per the subquery conditions.
Let’s check more different Examples of Subqueries
Consider You have a Table EmpMaater and you have to fetch the records only who’s Salary is greater than 25000 and, lets write SQL statement with Subquery using = operator in SQL
Subquery in SQL server Example
select ID,Name,Email,Salary,Mobile
from EmpMaster a where Salary =
(Select Salary from EmpMaster where a.ID=ID and Salary>25000)Subqueries in UPDATE and DELETE Statements:
Subquery is not noly for SQL select Statement but also used for Update and Delete SQL statement
How to use SUbqueries in Update and delete statement : you can delete or update the records in the table based on defination of subquery
How to Update records using Subquery
To update Records from the table using Subquery write below SQL statement.
SQL Update Query | SQL UPDATE Statement
Update Data using Subquery.
Update Employee set EmployeeAddress='Delhi East'
where EmployeeId in (Select ID from EmpMaster where ID=Employee.EmployeeID)Delete Records using Subquery.
Delete from Employee where
EmployeeID =(Select ID from EmpMaster where
Employee.EmployeeID=ID and ID >510)Subqueries with EXISTS and NOT EXISTS:
Subquery with Exists
select ID,Name,Email,Salary,Mobile from EmpMaster a where
exists (Select * from Employee where a.ID=EMployeeID)Subquery with Not Exists
select * from Employee where
not exists (select ID,Name,Email,Salary,Mobile
from EmpMaster a where a.ID=EMployeeID)Benefits of Subqueries in SQL:
Subquery allow us to perform complex SQL operations and get the result as per user requirement.
Subquery can be used to Fetch the records from another table with defined criteria.
Subqueries are used to write complex queries into easy and understandable SQL scripts.
You can fetch different columns from different table with writing Subqueries in single Main Query to retrieve data in single main query.
Subquery in SQL Server
Subqueries are used to check the data is present in another table which is selected in the Outer query by using different SQL statement.
SQL Subquery are most used feature in SQL server than can be used to perform comparisons of data, updating of data or checking for existence of records.
Subquery in SQL server Examples
-----Subquery Examples in SQL-----
select ID,Name,Email,Salary,Mobile
from EmpMaster a
where ID not in
(Select EmployeeID from Employee
where EmployeeCity='Mumbai')
select ID,Name,Email,Salary,Mobile
from EmpMaster a where Salary =
(Select Salary from EmpMaster where a.ID=ID and Salary>25000)
---Update using Subquery---
Update Employee set EmployeeAddress='Delhi East'
where EmployeeId in
(Select ID from EmpMaster where ID=Employee.EmployeeID)
---Delete using subquery---
Delete from Employee where
EmployeeID =(Select ID from EmpMaster where
Employee.EmployeeID=ID and ID >510)
---Exist using subquery---
select ID,Name,Email,Salary,Mobile from EmpMaster a where
exists (Select * from Employee where a.ID=EMployeeID)
---Not Exist using subquery---
select * from Employee where
not exists (select ID,Name,Email,Salary,Mobile
from EmpMaster a where a.ID=EMployeeID)