In my previous article i have given the basic idea about the scalar subqueries as well as correlated subqueries.In this article i would like to explain the nesting of SQL Queries. The queries where user needs to use nesting of SQL queries is known as SQL Nested Queries.SQL Nested Queries are nothing but SQL Queries where Query is written inside a query. By performance point of view SQL Nested Queries are not useful.
Definition of Nested Query :
Query written inside a query is called as SQL Nested Query
The user has question in mind that the query inside query will be Select query or any other query.There are Four types of nested queries.
1.Nested Queries with Select Statement
2.Nested Queries with Insert Statement
3.Nested Queries with Update Statement
4.Nested Queries with Delete Statement.
These are some most important types of Nested queries which we will look with example in next sections.
How to Write SQL Nested Queries :
Bullet-points needs to be considered while writing nested Queries :
1.The SQL Nested Query will be always enclosed inside the parentheses.
2.Nested sub-query can have only one column in select clause.
3.Order by clause is restricted in query which is inner query but outer query or main query can use order by clause.
4.User needs to take care of multiple rows operator (IN,ANY) if sub-query will return more than one rows.
5.Between–And Operator can not be used inside the Nested Query.
Type 1 : SQL Nested Queries with Select Statement
There are so many business situations where user needs to use nested subqueries to fetch the exact data from two or more tables.It is also called as Inline view in SQL.
Syntax :
Select Column1,Column2… From Table_Name
Where Column_Name Operator
(Select Column1,Column2…. From Table_Name_2)…
Operator (Select Column1,Column2…..From Table_Name_3)…
;
The user can use N Number of Inner Queries to fetch the required output. But using nesting of Queries is not a good practice for performance tuning perspective.
Real Life Examples of Nested Queries :
Following are two tables,
Employee Table :
| Employee No | Employee Name | Department |
| 1 | Rohan | SQL |
| 2 | Rajiv | PL SQL |
| 3 | Ram | Java |
Salary Table :
| Employee No | Salary |
| 1 | 25000 |
| 2 | 35000 |
| 3 | 45000 |
If user wants to fetch the all records of Employees who’s salary is greater that 25000.
In this case user needs to go for Nested Query.
Query :
Select * from Employee where Employee_No
In (Select Employee_No From Salary where Salary > 25000);
The Above Query is nested query which will give you the Employees data whose salary is greater than 25000.
Type 2 : SQL Nested Queries with Insert Statement
There are so many real life situations where user needs to use nested queries to insert the data in table. So many times user needs to use the testing and will need some special data.To tackle this situation Nested Queries with Insert statements will work.
Syntax :
Insert in to Tablename
(Select Column_1,Column2….From Tablename_1);
Real Life Example :
Let us consider the same tables given in Select Statement nested queries. User has created the replica of Employee table and needs the data where salary is greater than 25000. The Employee table replica name is Employee_Bkp.
Query :
Insert in to Employee_Bkp
(Select * from Employee where Employee_No
In (Select Employee_No From Salary where Salary > 25000));
The above nested query will help user to insert the required data in Employee_Bkp table.
Type 3 : SQL Nested Queries with Update Statement
There are sometimes user needs to update the data according to client requirements.If the data is not huge then user can use the nested queries to update the data.
Syntax :
Update Table Set Column_name =
(Select column1,Column2….From table);
Real Life Example :
I would like you to refer the above two tables only. User wants to change the name of Employee to Amit where Salary of that Employee is 25000.
Update Employee Set Name =
(
Select ‘Amit’ from Employee where Employee_No
= (Select Employee_No From Salary where Salary = 25000));
The above query will help you to update name to ‘Amit’.
Type 4 : SQL Nested Queries with Delete Statement
Sometimes user needs to delete the data with specific condition.So to delete the data with condition user needs to use Delete nested queries.
Syntax :
Delete from tablename
Where Column_Name Operator
(Select Columnname1,Columnname2… from Tablename2);
Real Life Example :
We need to delete data from Employee table where salary is greater than 25000.
Query :
Delete from Employee where Employee_No IN
( Select Employee_No From Salary where Salary > 25000 );
The above statement will delete the data from Employee table where salary is greater than 25000.
These are 4 different types of SQL Nested Queries.I hope this article will helpful to yo.If you like this article or if you have any suggestions with the same kindly comment in comments section.