SUBQUERIES

“Sub-queries are
useful when a query is
based on
Unknown values”
Using a Sub-query to Solve a Problem

“Who has a salary greater than Nancy ?”

Main Query

Which employee have a salary greater than

Nancy’s salary ?

Sub Query

“What is Nancy’s Salary ?

 Sub-queries
SELECT select_list
FROM table
WHERE { expr } operator
( SELECT select_list
FROM table );

• The sub-query (inner query ) executes once

before the main query.
• The result of the sub-query is used by the
main query (outer query).
 Using a Sub-queries
SELECT First_name,Salary
FROM employees
WHERE salary> 12000
(SELECT salary
FROM employee
WHERE First_name=‘Nancy);
FIRST_NAME SALARY
Steven 24000
Neena 17000
Lex 17000
John 14000
Karen 13500
Michael 13000
• Enclose the sub-queries in parenthesis.

• Place sub-queries on the right side of the

comparison operator.

• Do not add an ORDER BY clause to a sub-

query.

• Use single-row operators with single-row sub-

queries.

• Use multiple-row operators with multiple-row

sub-queries.
Type of Sub-queries
Single-row sub-query:
1. Returns only one row
2. Use single-row comparison operators

Multiple-row sub-query :
3. Return more than one row.
4. Use multiple-row comparison operators.

Multi-column sub-query:
5. A multiple-column
subquery returns more than one column.
6. Column comparisons in multi-column sub-query can be pairwise or nonpairwise.
 Single-Row Sub-queries
• Returns 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
 Executing Single-Row Sub-queries
SELECT First_name, job_id,salary
FROM employees
WHERE job_id= ST_CLERK
(SELECT job_id
FROM employees
WHERE employee_id=133)
AND salary >
(SELECT salary 2400
FROM employees
WHERE employee_id=135)
FIRST_NAME JOB_ID SALARY
Julia ST_CLERK 3200
Irene ST_CLERK 2700
Laura ST_CLERK 3300
….. ….. …..
Using Group Functions in a Sub-query

SELECT First_name, job_id,salary

FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees);

FIRST_NAME JOB_ID SALARY

TJ ST_CLERK 2100
 Having clause with Sub-queries
• The ORACLE Server executes sub-queries
first.
• The ORACLE Server returns results into the
HAVING clause of the main query.

SELECT department_id, MIN(salary)

FROM employees
GROUP BY department_id
6100
HAVINGMIN(salary) >
(SELECT MIN(salary)
FROM employees
WHERE department_id = 80);
What Is Wrong with This Statement ?
SELECT empno,ename
FROM emp
WHERE sal =
(SELECT MIN(sal)
FROM emp
GROUP BY deptno);

ERROR:
ORA – 01427:single-row sub-query returns more than
one row.

No rows selected
 Multiple-Row Sub-queries
• 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 sub-query
ALL Compare value to every value
returned by the sub-query.
 Using ANY Operator in
Multiple-Row Sub-queries
SELECT employee_id, First_name, job_id
FROM employees
WHERE salary > ANY
(SELECT salary
FROM employees
WHERE job_Id = ‘SA_MAN’);
EMPLOYEE_ID FIRST_NAME JOB_ID
100 Steven AD_PRES
102 Lex AD_VP
146 Karen SA_MAN
201 Michael MK_MAN
108 Nancy FI_MGR
….. ….. ……
Using ALL Operator in Multiple-Row
Sub-queries
SELECT employee_id, First_name, job_id
FROM employees
WHERE salary > ALL
(SELECT avg(salary)
FROM employees
GROUP BY department_id);

EMPLOYEE_ID FIRST_NAME JOB_ID

100 Steven AD_PRES
Multi-column sub-query:
1. A multiple-column subquery
returns more than one column.
2. Column comparisons in multi-
column sub-query can be
pairwise or nonpairwise.
Multi-column sub-query:
Pairwise
Display the order number, product number, and quantity of any item in which the
product number and quantity match both the product number and quantity of an
item in ordid 365.
SELECT  ordid, prodid, qty
FROM           item
WHERE        (prodid,   qty)    IN
                                        (SELECT prodid,   qty
                                         FROM        item
                                         WHERE     ordid =   365)
AND  ordid   =   365 ;

ORDID PRODID QTY

365 84 22
Multi-column sub-query:
Nonpairwise Comparison Subquery
SELECT        ordid, prodid, qty                 
  FROM          item                
  WHERE       prodid  IN  (SELECT            prodid            
                                            FROM   Item   
                                            WHERE           ordid  = 365)
  AND  qty  IN  (SELECT    qty      
                            FROM       item    
                            WHERE    ordid = 365)
  AND  ordid  =  365 ;          

ORDID PRODID QTY

365 84 22
End of Session