Subqueries
SELECT select_list
FROM table
WHERE expr operator
( SELECT select_List
FROM table );
•The subquery (inner query) executes
once before the main query.
•The result of the subquery is used
by the main query (outerquery).
A subquery is a SELECT statement that is embedded in a clause of another
SELECT statement. You can build powerful statements out of simple ones by
using subqueries. They can be very useful when you need to select rows from
a table with a condition that depends on the data in the table itself.
You can place the subquery in a number of SQL clauses
•WHERE clauss
•HAVING clause
•FROM clause
ln the syntax;
operator includes a comparison operatör such as >, =, or IN
Note: Comparison operutors fall iııto two classes: single-row operators
( > , = , >= , < , < > , <= )
and multiple-row operators ( IN , ANY , ALL ).
Using a Subquery
SELECT ename
FROM EMP
WHERE sal >
( SELECT sal FROM
emp WHERE
empno=7566);
ENAME
FORD
SCOTT
KING
FORD
Using a Subquery
SELECT ename, sal, deptno, job
FROM EMP
WHERE job =
( SELECT job FROM
emp WHERE
empno=7369);
ENAME SAL DEPTNO JOB
ADAMS 1100 20 CLERK
JAMES 950 30 CLERK
MILLER 1300 10 CLERK
SMITH 800 20 CLERK
ADAMS 1100 20 CLERK
JAMES 950 30 CLERK
MILLER 1300 10 CLERK
7 rows selected.
SELECT ename, sal, deptno
FROM EMP
WHERE sal IN
( SELECT MIN(sal)
FROM emp
GROUP BY deptno );
ENAME SAL DEPTNO
JAMES 950 30
SMITH 800 20
MILLER 1300 10
SELECT empno, ename, job
FROM emp
WHERE sal < ANY
( SELECT sal
FROM emp
WHERE job = 'CLERK' );
EMPNO ENAME JOB
7369 SMITH CLERK
7900 JAMES CLERK
7876 ADAMS CLERK
7521 WARD SALESMAN
7654 MARTIN SALESMAN
SELECT empno, ename, job
FROM emp
WHERE sal < ANY
( SELECT sal
FROM emp
WHERE job = 'CLERK' )
AND job <> 'CLERK' ;
EMPNO ENAME JOB
7521 WARD SALESMAN
7654 MARTIN SALESMAN
SELECT empno, ename, job
FROM emp
WHERE sal > ALL
( SELECT sal
FROM emp
WHERE job = 'CLERK' )
AND job <> 'CLERK' ;
EMPNO ENAME JOB
7499 ALLEN SALESMAN
7566 JONES MANAGER
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7902 FORD ANALYST
8 rows selected.
SELECT empno, ename, job
FROM emp
WHERE sal > ALL
( SELECT AVG(sal)
FROM emp
GROUP BY deptno ) ;
EMPNO ENAME JOB
7566 JONES MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7902 FORD ANALYST
Guidelines for Using Subqueries
•Enclose subqueries in parentheses.
•Place subqueries on the right side of
the comparison operator.
•Do not add an ORDER BY clause to
a subquery.
•Use single-row operators with
single-row subqueries.
•Use multiple-row operators with
multiple-row subqueries.
Single-Row Subqueries
•Return only one row
•Use single-row comparison operators
Operator Meaning
=
Equal to
>
Greater than
>=
Greater than or equal to
<
Less than
<=
<> , =! Less than or equal to
Not equal to
SELECT ename, job
FROM EMP
WHERE job =
( SELECT job
FROM emp
WHERE empno=7369 ) ;
ENAME JOB
ADAMS CLERK
JAMES CLERK
MILLER CLERK
SMITH CLERK
ADAMS CLERK
JAMES CLERK
MILLER CLERK
7 rows selected
Using Group Functions in a Subquery
SELECT ename, sal, deptno
FROM EMP
WHERE sal IN
( SELECT MIN(sal)
FROM emp GROUP
BY deptno ) ;
ENAME SAL DEPTNO
SMITH 800 20
JAMES 950 30
MILLER 1300 10
HAVING Clause with Subqueries
•The Oracle Server executes
subqueries first.
•The Oracle Server returns results into
the HAVING clause of the main query.
SELECT job, AVG(sal)
FROM emp
GROUP BY job
HAVING AVG(sal) =
( SELECT MIN(AVG(sal))
FROM emp
GROUP BY job );
Multiple-Row Subqueries
Return more than one row
Use multiple-row comparison operators
Operator Meaning
IN Equal to any member in the list
ANY Compare value to each value returned by the subquery
ALL Compare value to every value returned by the subquery
SELECT ename, sal, deptno
FROM emp
WHERE sal IN (SELECT MIN(sal)
FROM emp
GROUP BY deptno);
ENAME SAL DEPTNO
SMITH 800 20
JAMES 950 30
MILLER 1300 10
Using ANY Operator in
Multiple-Row Subqueries
SELECT ename, sal, job
FROM emp
WHERE sal < ANY
( SELECT sal
FROM emp
WHERE job = 'CLERK' )
AND
job <> 'CLERK' ;
ENAME SAL JOB
WARD 1250 SALESMAN
MARTIN 1250 SALESMAN
Using ALL Operator in
fgbtrg
Multiple-Row Subqueries
SELECT ename, sal, job
FROM emp
WHERE sal > ALL
( SELECT AVG(sal)
FROM emp
GROUP BY deptno );
Creating subqueries to query values based on unknown criteria
Using subqueries to find out what values exist in one set of
data and not in another
1. Write a query to display the employee name and hiredate for
all employees in the same department as Blake. Exclude Blake.
SELECT ename, hiredate
FROM emp
WHERE deptno =
( SELECT deptno
FROM emp
WHERE ename = 'BLAKE')
AND ename <> 'BLAKE';
2. Create a query to display the employee number and name for all employees who earn
more than the average salary. Sort the results in descending order of salary.
SELECT empno, ename
FROM emp
WHERE sal >
( SELECT AVG(sal)
FROM emp );
3. Write a query to display the employee number and name for all
employees who work in a department with any employee whose name
contains a T. Save your SQL statemant in a file called p6q3.sql .
SELECT empno, ename
FROM emp
WHERE deptno IN
( SELECT deptno
FROM emp
WHERE ename LIKE '%T%' );
24
4. Display the employee name, department number, and job title for
all employees whose department location is Dallas.
Solution with subquery:
SELECT ename, empno, job
FROM emp
WHERE deptno = (SELECT deptno
FROM dept
WHERE loc ='DALLAS') ;
Solution with equijoin:
SELECT ename, empno, job
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND d.loc='DALLAS';
ENAME EMPNO JOB
SMITH 7369 CLERK
JONES 7566 MANAGER
SCOTT 7788 ANALYST
ADAMS 7876 CLERK
FORD 7902 ANALYST
5. Display the employee name and salary of all employees who report to
King.
Solution with self join:
SELECT e.ename, e.sal
FROM emp e , emp d
WHERE e.mgr = d.empno
AND
d.ename ='KING';
Solution with subquery:
25
SELECT ename, sal
FROM emp
WHERE mgr = (SELECT empno
FROM emp
WHERE ename = 'KING' );
6. Display the department number, name,, and job for all employees in the
Sales department.
SELECT e.deptno, e.ename, e.job , d.dname
FROM emp e , dept d
WHERE e.deptno = d.deptno
AND
d.dname = 'SALES'
If yo u have time, complete the following exercises:
7. Modify p6q3.sql to display the employee number, name, and salary for
all employees who earn more than the average salary and who work in a
department with any employee with a T in their name. Rerun your query.
SELECT empno, ename , sal
FROM emp
WHERE sal > (SELECT AVG (sal)
FROM emp )
AND
deptno IN ( SELECT deptno
FROM emp
WHERE ename LIKE '%T%');
EMPNO ENAME SAL
7902 FORD 3000
7788 SCOTT 3000
7566 JONES 2975
7698 BLAKE 2850
