Using Subqueries to solve Queries
- Using a Subquery to solve a Problem
Find out who has a salary greater than the EMP ‘SMITH’
In order to fetch this we need to divide them into two sections :
- Find the Salary of SMITH
- Find those Salaries greater than SMITH’s Salary.
We need to write two queries to execute this process.
In order to avoid this we can use a subquery to fetch the results.
INNER QUERY – Select Salary of SMITH
OUTER QUERY – Select Salary which is greater than SMITH’s Salary.
INNER Query executes first and returns those rows to outer query to process the results.
The Subquery executes once before the main query.The results of the subquery is used by the main query.
— Ex
SELECT EMPNO,ENAME,SAL
FROM EMP
WHERE SAL >
(SELECT SAL FROM EMP WHERE ENAME = ‘SMITH’)
/
SELECT EMPNO,ENAME,SAL
FROM EMP
WHERE SAL >=
(SELECT SAL FROM EMP WHERE ENAME = ‘SMITH’)
/
Subquery — You can nest upto 255 levels of Subqueries
2.
Types of SUBQUERIES
Single – Row Subquery : Returns single set of records from the inner Query.
Multiple – Row Subquery : Returns multiple set of records from the inner query.
Single – Row Subquery
Returns only one row and uses single-row comparison operators.
— Ex
SELECT ENAME,JOB
FROM EMP
WHERE JOB =
(SELECT JOB FROM EMP WHERE EMPNO = 7369)
/
— Find JOB of EMP No. 7369 —
SELECT job FROM epm WHERE empno = 7369
/
— Find SAL of EMP No. 7369 —
SELECT sal FROM emp WHERE empno = 7369
/
— Find the details from EMP where JOB = ‘CLERK’ and SAL is greater than the SALARY earned by the Employee no. 7369 —
SELECT empno,ename,sal,job
FROM emp
WHERE job =
(SELECT job FROM emp WHERE empno = 7369)
AND sal >
(SELECT sal FROM emp WHERE empno = 7369)
/
3.
Using Group Functions in a Subquery
Display data from a main query by using a Group Function in a subquery to return a single row.
— Query to display Employee Name,JOB,and Salary of all employees whose salary is equal to the minimum salary.
SELECT empno,ename,job,sal
FROM emp
WHERE sal = (SELECT MIN(sal) FROM emp)
/
HAVING Clauses in a SUBQUERY
Inner Query gets executed first and then these results are passed to the OUTER Query’s HAVING Clause.
— Query to find the all the departments having a min salary less than deptno 10 —
SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 10
/
SELECT DEPTNO,MIN(SAL)
FROM EMP
GROUP BY DEPTNO
/
SELECT DEPTNO,MIN(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING MIN(SAL) <
(SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 10)
/
Ex
SELECT empno,ename
FROM EMP
WHERE SAL = (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO)
/
To avoid this error instead of using the = operator use the IN operator.
SELECT empno,ename,sal,deptno
FROM EMP
WHERE SAL IN (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO)
/
— Query to display the Rounded Value of a Maximum Average Salary department wise —
SELECT ROUND(MAX(AVG(sal))) max
FROM emp
GROUP BY deptno
/
— Query to display the Employee Details who have a Salary equal to Maximum Average Salary department wise —
SELECT empno,ename,sal,deptno
FROM EMP
WHERE sal = (SELECT ROUND(MAX(AVG(sal))) FROM emp GROUP BY deptno)
/
SELECT empno,ename,sal,deptno
FROM EMP
WHERE sal < (SELECT ROUND(MAX(AVG(sal))) FROM emp GROUP BY deptno)
/
Another problem with Subquery is if the Inner Query returns NULL to the Outer Query the Result of the QUERY is NULL.
4.
Multiple Row Subquery
These subqueries return more than one row and use multiple-row comparison operators.
IN – Equal to any row in the list.
ANY – Compare value to each value returned by the subquery
ALL – Compare value to every value returned by the subquery
IN
SELECT empno,ename
FROM EMP
WHERE SAL IN (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO)
/
Oracle Server executes this in the following way
SELECT EMPNO,ENAME
FROM EMP
WHERE SAL IN (950,800,1300)
< ANY — Will return those values which are lesser than the maximum value returned from the Inner Subquery.
ANY – Will return those values which are greater than the minimum value returned from the Inner Subquery.
SELECT SAL,DEPTNO FROM EMP WHERE DEPTNO = 10
/
SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP
WHERE SAL > ANY
(SELECT SAL FROM EMP WHERE DEPTNO = 10)
/
SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP
WHERE SAL < ANY
(SELECT SAL FROM EMP WHERE DEPTNO = 10)
/
SELECT SAL,DEPTNO FROM EMP WHERE DEPTNO = 10 ORDER BY SAL
/
ALL — Will return all values which are greater than the maximum values returned by the Inner Subquery.
< ALL — Will return those values which are lesser than the minimum values returned by the Inner Subquery.
ALL –
SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP
WHERE SAL > ALL
(SELECT SAL FROM EMP WHERE DEPTNO = 10)
ORDER BY SAL
/
SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP
WHERE SAL < ALL
(SELECT SAL FROM EMP WHERE DEPTNO = 10)
ORDER BY SAL
/
— Query to return the Salary greater than all the Salaries —
SELECT empno,sal
FROM emp
WHERE sal > ALL (2000,3000,4000)
/
— Transformed to equivalent statement without ALL.
SELECT empno,sal
FROM emp
WHERE sal > 2000 AND sal > 3000 AND sal > 4000
/
5.
NULL Values in a Subquery
Ex
SELECT emp.ename
FROM emp
WHERE emp.empno NOT IN
(SELECT a.mgr
FROM EMP a)
/
This query will not return rows because the INNER Query has returned NULL Values
So whenever NULL values are likely to be part of the inner query do not use a NOT IN operator.
So instead we can use the IN Operator
— Display Employees who have subordinates —
SELECT emp.ename,emp.job
FROM emp
WHERE emp.empno IN
(SELECT mgr.mgr
FROM emp mgr)
/
— Alternatively to display those employees who do not have subordinates —
SELECT emp.ename,mgr,job
FROM emp
WHERE emp.empno NOT IN
(SELECT MGR.MGR
FROM EMP MGR WHERE MGR IS NOT NULL)
/
— A Column in a Select Clause used as a Subquery —
SELECT (SELECT DEPTNO FROM DEPT WHERE DEPTNO = 10) “Department No.”,ENAME
FROM EMP
/
q Operator in Oracle —
— Display the String as Oracle’s Quote Operator —
SELECT ‘Oracle’s Quote Operator’ FROM DUAL
/
SELECT q’#Oracle’s Quote Operator#’ FROM DUAL
/
SELECT q’^Oracle’s Quote Operator^’ FROM DUAL
/
SELECT INITCAP(ENAME)||q’#’s#’||’ Salary is: ‘||SAL
FROM EMP
6.
Inline View
When we use SQL Subquery in the FROM clause of the SELECT statement it is called an Inline View.
In-Line Views are used to simplify complex queries by removing join operations and filtering several separate queries into a single query.
SELECT *
FROM emp e,(SELECT deptno FROM dept) d
WHERE e.deptno = d.deptno
/
SELECT dept.dname,emp.emp_count “Count”
FROM (SELECT deptno,COUNT(*) emp_count
FROM emp
GROUP BY deptno) emp,dept
WHERE dept.deptno = emp.deptno
/