Sub Query

Subquery is a query that is nested inside a SELECT, UPDATE or DELETE statements, or inside another subquery. A subquery can be used anywhere an expression is allowed. We can have upto 32 levels of subqueries. Subqueries are slower than joins. A subquery can return a scalar value or a series of value.

Subqueries are of 2 types:
1. Standard or Non Correlated Subqueries
2. Correlated Subqueries

Below is the sample dataset to demonstrate the subqueries.

IF EXISTS (SELECT * FROM sys.tables WHERE name='Employee')

DROP TABLE Test.dbo.Employee

 CREATE TABLE Test.dbo.Employee

 (

     EmpID INT,

        EmpName VARCHAR(100),

        EmpDept VARCHAR(100),

        EmpSal INT

)

INSERT INTO Test.dbo.Employee VALUES (1,'AAA','HR',10000)

INSERT INTO Test.dbo.Employee VALUES (2,'BBB','HR',20000)

INSERT INTO Test.dbo.Employee VALUES (3,'CCC','HR',40000)

INSERT INTO Test.dbo.Employee VALUES (4,'DDD','IT',23000)

INSERT INTO Test.dbo.Employee VALUES (5,'EEE','IT',42789)

INSERT INTO Test.dbo.Employee VALUES (6,'FFF','IT',12568)

INSERT INTO Test.dbo.Employee VALUES (7,'GGG','IT',28000)

INSERT INTO Test.dbo.Employee VALUES (8,'HHH','HR',80000)

INSERT INTO Test.dbo.Employee VALUES (9,'III','FIN',25670)

INSERT INTO Test.dbo.Employee VALUES (10,'JJJ','FIN',34000)

INSERT INTO Test.dbo.Employee VALUES (11,'KKK','FIN',60000)

INSERT INTO Test.dbo.Employee VALUES (12,'LLL','FIN',10000)

INSERT INTO Test.dbo.Employee VALUES (13,'MMM','FIN',10000)

Non Correlated subqueries
A non correlated subquery is one which is independent of the outer query and it can be executed on its own without depending on the outer query.
In the below example we can execute "SELECT AVG(EmpSal) FROM Test.dbo.,Employee" can be executed independently.

Correlated subqueries

A correlated subquery is one where inner query and the outer query are related with each other. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query. Inner query and outer query are not independent to execute.