SQL SERVER – Sub Query

SUB QUERY:- SubQuery is nothing but Query within a Query (or) it is a part of a Query.

There are 3 types of Sub queries exists in SQL.

1.SINGLE-VALUED SUB QUERY:-

                                      If we get a single value as an output from a Sub query of a Query then it is called as Single Valued Sub query.

Example:

CREATE TABLE EMP

(

       EMPID INT

      ,EMPNAME VARCHAR(50)

      ,SAL MONEY

      ,DOJ DATE

      ,LOC VARCHAR(50)

      ,GENDER CHAR(6)

      ,MagrID INT

      ,DEPTNO INT

)

INSERT INTO EMP(EMPID, EMPNAME, SAL, DOJ, LOC, GENDER, MagrID, DEPTNO)

VALUES(1,'Santhosh',35000,'01-12-2014','Bangalore','M',3,101)

INSERT INTO EMP

VALUES(2,'Kumari',30000,'11-05-2013','Chennai','F',3,104)

INSERT INTO EMP(EMPID, EMPNAME, SAL, DOJ, LOC, GENDER, MagrID, DEPTNO)

VALUES(3,'Kamesh',75000,'01-12-2012','Mumbai','M',NULL,105)

INSERT INTO EMP(EMPID, EMPNAME, SAL, LOC, GENDER, MagrID, DEPTNO)

VALUES(4,'Arun',44000,'Hyderabad','m',5,103)

INSERT INTO EMP(EMPID, EMPNAME, DOJ, LOC, GENDER, MagrID, DEPTNO)

VALUES(5,'Aishwarya','01-12-2014','Bangalore','f',3,103)

To get info of maximum salary employee:

SELECT * FROM EMP

WHERE SAL= (SELECT MAX(SAL) FROM EMP )  

2.MULTI-VALUED SUB QUERY

 If we get multiple values as an output from a Sub query of a Query then it is called as Multi-Valued Sub query.

Example:-
              To retrieve info of all employees whose sal is more than 50000

SELECT * FROM EMP

WHERE SAL IN (SELECT SAL FROM EMP WHERE SAL>50000)

3. CO-RELATED SUB QUERY

If both Query and Sub query runs parallel that means both Query and Sub query depends on each other’s output then it is called as Co-related Sub query. 

Example:-

-- To retrieve info of second maximum salary employee

SELECT * FROM EMP E1

WHERE 2=(SELECT DISTINCT COUNT(*) FROM EMP E2 WHERE E1.SAL<=E2.SAL)

-- To retrieve info of second minimum salary employee

SELECT * FROM EMP E1

WHERE 2=(SELECT DISTINCT COUNT(*) FROM EMP E2 WHERE E1.SAL>=E2.SAL)