SubQueries in SQL

SubQueries in SQL

Sub Queries are the queries within another  SQL query(outer query or parent query). You can have any number of layers of inner queries. While executing  the SQL, the innermost query is executed first and gradually executes the outer queries . The outer queries might use the data from the subqueries(inner queries).

Once you understand the nature of sub query , you can use it anywhere in your SQL as per  your need and appropriateness of using the subquery . We are discussing the use of the subquery in below clauses:

SELECT

FROM

WHERE

HAVING

Subquery is also a complete query . As already mentioned, when the SQL is executed , the subquery  is executed first  and produces result that the outer query uses.

Subquery in SELECT :

TABLE1: INFO_EMP

BATCH_ID	NAME	JOB_NUMBER	POSITION	DEPARTMENT	HIREDATE
100	DAVID	143658	MANAGER	HR	10-Jan-15
103	SMITH	548900	ENGINEER	TECHNICAL	12-Dec-11
101	LINDA	784510	ASSISTANT	FINANCE	1-Jun-12
102	MIKE	543433	OPERATOR	TECHNICAL	1-Feb-12
NULL	SMITH	543433	ASSISTANT	TECHNICAL	2-Nov-13
NULL	SHARMA	543401	OFFICER	DEPLOYMENT	22-Aug-15

TABLE2 : TEST

EMP_ID	LOCATION	DEPT	SALARY
101	MD	NULL	2000
100	Chicago	NULL	1950.65
102	NY	NULL	3000
103	NY	NULL	NULL

Example1:

Below query uses  SubQuery  in SELECT  to fetch Location for the employees :

Select BATCH_ID , NAME , (SELECT A.LOCATION FROM TEST A  WHERE INFO_EMP.BATCH_ID = A.EMP_ID ) AS LOCATION , POSITION , HIREDATE FROM INFO_EMP ;

OutPut:

BATCH_ID	NAME	LOCATION	POSITION	HIREDATE
100	DAVID	Chicago	MANAGER	10-Jan-15
103	SMITH	NY	ENGINEER	12-Dec-11
101	LINDA	MD	ASSISTANT	1-Jun-12
102	MIKE	NY	OPERATOR	1-Feb-12
NULL	SMITH	NULL	ASSISTANT	2-Nov-13
NULL	SHARMA	NULL	OFFICER	22-Aug-15

Example2:

Let's  find Average salary , Name , Batch ID , Position, HireDate :

Select BATCH_ID, NAME , ( SELECT AVG(SALARY) FROM TEST ) AS AVG_SAL , POSITION , HIREDATE

FROM INFO_EMP;

OutPut:

BATCH_ID	NAME	AVG_SAL	POSITION	HIREDATE
100	DAVID	2316.883	MANAGER	10-Jan-15
103	SMITH	2316.883	ENGINEER	12-Dec-11
101	LINDA	2316.883	ASSISTANT	1-Jun-12
102	MIKE	2316.883	OPERATOR	1-Feb-12
NULL	SMITH	2316.883	ASSISTANT	2-Nov-13
NULL	SHARMA	2316.883	OFFICER	22-Aug-15

Example3:

Select BATCH_ID, NAME , (( SELECT AVG(SALARY) FROM TEST ) +1000 ) AS BONUS, POSITION , DEPARTMENT

FROM INFO_EMP ;

OutPut:

BATCH_ID	NAME	BONUS	POSITION	DEPARTMENT
100	DAVID	3316.883333	MANAGER	HR
103	SMITH	3316.883333	ENGINEER	TECHNICAL
101	LINDA	3316.883333	ASSISTANT	FINANCE
102	MIKE	3316.883333	OPERATOR	TECHNICAL
NULL	SMITH	3316.883333	ASSISTANT	TECHNICAL
NULL	SHARMA	3316.883333	OFFICER	DEPLOYMENT

SubQuery in WHERE :

EXAMPLE 1:

Find the employee information from INFO_EMP table , the employee who is having maximum salary in TEST table.  In below query, you can see , we have used two layers of the subqueries.

Select  NAME , POSITION , DEPARTMENT , HIREDATE

FROM INFO_EMP

WHERE BATCH_ID = ( SELECT EMP_ID FROM TEST WHERE SALARY = ( SELECT MAX(SALARY) FROM TEST ));

OutPut:

NAME	POSITION	DEPARTMENT	HIREDATE
MIKE	OPERATOR	TECHNICAL	1-Feb-12

EXAMPLE 2:

Find employee information from 'TECHNICAL' department.

Select  NAME , POSITION , DEPARTMENT , HIREDATE

FROM INFO_EMP

WHERE JOB_NUMBER IN ( SELECT JOB_NUMBER FROM INFO_EMP WHERE DEPARTMENT = 'TECHNICAL');

OutPut:

NAME	POSITION	DEPARTMENT	HIREDATE
SMITH	ENGINEER	TECHNICAL	12-Dec-11
SMITH	ASSISTANT	TECHNICAL	2-Nov-13
MIKE	OPERATOR	TECHNICAL	1-Feb-12

Sub Query in HAVING clause :

Let's take a table EMP_INFO:

NAME	DEPARTMENT	SALARY	POSITION	STATE	JOINDATE
DAVID	MARKETING	2000	MANAGER	IL	12-Dec-00
RICHARD	SALES	1000	SALESMAN	MD	1-Jan-08
SMITH	RESOURCES	1500	HR	TX	5-Jan-06
RAM	RESOURCES	1200	ASSISTANT	TX	5-Jun-06
GOPI	SALES	1200	MARKETER	FL	9-Feb-08
BRITNI	TECHNICAL	1800	ASSISTANT	PA	1-Feb-09
ADAM	TECHNICAL	2200	LEAD	MD	1-Mar-10
MIKE	EXECUTIVE	2500	DIRECTOR	IL	1-Aug-10
JAMES	MARKETING	1200	ACCOUNTANT	VA	20-Aug-14
JULI	EXECUTIVE	2200	MANAGER	VA	21-Aug-12

Let's find  the departments having average salary greater than the average salary of all department.

Select  DEPARTMENT , AVG(SALARY)  AS AVG_SAL

FROM EMP_INFO

GROUP BY DEPARTMENT

HAVING AVG(SALARY) > (SELECT AVG(SALARY) FROM EMP_INFO);

OutPut:

DEPARTMENT	AVG_SAL
TECHNICAL	2000
EXECUTIVE	2350

Subquery in FROM :

Let's find employee name , department , state , salary from the subquery which filters out the data with salary 2000 or more.

Select  NAME, DEPARTMENT, STATE , SALARY  FROM (SELECT * FROM EMP_INFO WHERE SALARY <2000 p="">

OutPut:

NAME	DEPARTMENT	STATE	SALARY
RICHARD	SALES	MD	1000
SMITH	RESOURCES	TX	1500
RAM	RESOURCES	TX	1200
GOPI	SALES	FL	1200
BRITNI	TECHNICAL	PA	1800
JAMES	MARKETING	VA	1200

Let's have a TEST table as below :

EMP_ID	LOCATION	DEPT	SALARY
101	MD	NULL	2000
100	VA	NULL	1950.65
102	PA	NULL	3000
103	NY	NULL	NULL

Now, let's find the employee NAME, DEPARTMENT, STATE from the two tables EMP_INFO and TEST  for the states which are common to two tables.

Select  *  FROM (SELECT A.NAME , A.DEPARTMENT, A.STATE  FROM EMP_INFO A , TEST B  WHERE A.STATE = B.LOCATION ) ;

OutPut:

NAME	DEPARTMENT	STATE
RICHARD	SALES	MD
BRITNI	TECHNICAL	PA
ADAM	TECHNICAL	MD
JAMES	MARKETING	VA
JULI	EXECUTIVE	VA

Let's find out the Name and Department from the above result , using subquery.  We are interested to know the employee name from other than TECHNICAL department even though the subquery returns data/information for Technical department also.

Select  NAME, DEPARTMENT  FROM (SELECT A.NAME , A.DEPARTMENT, A.STATE  FROM EMP_INFO A , TEST B  WHERE A.STATE = B.LOCATION )

WHERE DEPARTMENT !='TECHNICAL';

OutPut:

NAME	DEPARTMENT
RICHARD	SALES
JAMES	MARKETING
JULI	EXECUTIVE