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
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)