Sub queries in SQL

Sub queries with examples in SQL

Sub queries is defined as queries with in other queries .The outermost select query whose result has to be displayed is called outer query.The outer query uses the Result of inner query.

There are two types of sub queries Self contained sub queries and Correlated sub queries.

1. A self contained query has no dependency on outer query .The outer query will display result based on inner query Result.
.If you will Run the inner sub query separetely it will display the result

2.A correlated sub query is that when the inner query is dependent on Outer query. That mean the Subquery
will use the values form outer query. It's like a Search Condition in a Sub query.

so in order to demonstrate Self contained Sub query and correlated queries we have a table script below.

CREATE TABLE [dbo].[Employee_Correlated](
 [Empid] [int]  NOT NULL,
 [EmpName] [nvarchar](50) NULL,
 [EmpSalary] [float] NULL
) ON [PRIMARY]
GO

INSERT [dbo].[Employee_Correlated]  ([Empid], [EmpName], [EmpSalary]) VALUES (1, N'sumit', 15000)
INSERT [dbo].[Employee_Correlated]  ([Empid], [EmpName], [EmpSalary]) VALUES (2, N'amit', 18000)
INSERT [dbo].[Employee_Correlated]  ([Empid], [EmpName], [EmpSalary]) VALUES (3, N'vijay', 10000)
INSERT [dbo].[Employee_Correlated]  ([Empid], [EmpName], [EmpSalary]) VALUES (4, N'suresh', 5000)
INSERT [dbo].[Employee_Correlated]  ([Empid], [EmpName], [EmpSalary]) VALUES (5, N'Mukesh', 3000)
INSERT [dbo].[Employee_Correlated]  ([Empid], [EmpName], [EmpSalary]) VALUES (1, N'sumit', 25000)
INSERT [dbo].[Employee_Correlated]  ([Empid], [EmpName], [EmpSalary]) VALUES (2, N'amit', 28000)
INSERT [dbo].[Employee_Correlated]  ([Empid], [EmpName], [EmpSalary]) VALUES (3, N'vijay', 17000)
INSERT [dbo].[Employee_Correlated]  ([Empid], [EmpName], [EmpSalary]) VALUES (4, N'suresh', 55000)
INSERT [dbo].[Employee_Correlated]  ([Empid], [EmpName], [EmpSalary]) VALUES (5, N'Mukesh', 33000)



Case I -Self Contained Subqueries

Now we want to show Max salary from Employee table along with his name
so in this case we will use Self contained sub query.

--Self contained Self query
select * from [Employee_Correlated] where EmpSalary =(select MAX(Empsalary) from [Employee_Correlated])

Output
Empid EmpName EmpSalary
4 suresh 55000

If you will run the innner query alone it will run succesfully displaying max salary of the Table

Case II Correlated Sub queries

Now we want to show all the Employees with their Max salary
So in this case we will use Correlated sub queries.

--correlated sub queries.
select * from [Employee_Correlated] where EmpSalary in (select MAX(EmpSalary) from [Employee_Correlated] E
where E.Empid = [Employee_Correlated].EmpID)

Output

Empid EmpName EmpSalary
5 Mukesh 33000
4 suresh 55000
3 vijay 17000
2 amit 28000
1 sumit 25000

Now try to run the inner query alone.
It will throw an error

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "Employee_Correlated.EmpID" could not be bound.

this shows that in correlated sub query the inner query is using the values from outer query.

NOTE:You cannot use Order by with Subquery or Inner query however you can use order by with Outer query.