Sub Queries [ Uncorrelated Sub Queries ]

A Subquery as the name suggests , is an Inner Query nested or embedded within another SQL Query also called Outer Query.

Key Facts about Sub Queries.

• The Inner Query or the Subquery ,is always written after the WHERE or HAVING clause of Outer Query.
• The Subquery is always enclosed in parenthesis.
• The Inner Query or a Subquery is always a SELECT statement nested in Outer Query which can be a  SELECT, INSERT , UPDATE or DELETE Statement.
• SQl executes the Inner Query first , then Outer Query uses the results of the Inner Query.
• BETWEEN can be used within Subquery but cannot be used with Subquery.

Note : The Inner Query can execute independently of the Outer Query i.e it is not dependent on the Outer Query for its execution, hence these normal Subqueries are also termed as “UNCORRELTED SUB QUERIES “

Types of Sub Queries

Single Column Sub Query :

Multiple Column Sub Query :

Nested Sub Query :

• The Inner Query at times can also be nested in another Subquery which in turn may be nested in another Subquery and so on.
• Maximum 255 Queries can be nested in a Subquery.

Column Level Sub Query :

Important Sub Query Examples :

Ques 1. Display the names of the employees getting Highest Salaries.

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

Ques 2. Display the names of the employees getting Second Highest Salaries.

SELECT ENAME  FROM EMP WHERE SAL = (SELECT MAX(SAL) FROM EMP WHERE SAL < (SELECT     MAX(SAL) FROM EMP));

Ques 3. Display the names of the employees who are located in ‘CHICAGO’ . ( Location is present in                   DEPT Table and Employees name in EMP Table) .

SELECT ENAME FROM EMP WHERE DEPTNO = ( SELECT DEPTNO FROM DEPT WHERE LOC =                           ‘CHICAGO’);

Error – Single row Subquery returns more than 1 row.

Correct Ans : SELECT ENAME FROM EMP WHERE DEPTNO IN ( SELECT DEPTNO FROM DEPT WHERE LOC =                         ‘CHICAGO’);

Ques 4. Display Employee details i.e.Employee names , Empno , salaries along with the highest salary .

SELECT ENAME,EMPNO,SAL , MAX(SAL) FROM EMP;

Error – Single group function. To display any other column along with aggregate function, group by has to be used.

Correct Ans : SELECT ENAME,EMPNO,SAL ,(SELECT MAX(SAL) FROM EMP ) HIGH_SAL FROM EMP;

Ques 5. Display Employee details i.e.Employee names , Empno , salaries along with the highest salary  and the difference between the highest salary and salary for each employee.

SELECT ENAME,EMPNO,SAL ,(SELECT MAX(SAL) FROM EMP ) HIGH_SAL,((SELECT MAX(SAL) FROM EMP) – SAL) DIFF_SAL FROM EMP;