Query Nested (Sub Query)
Nested Query
Consider the relations:
Employees (ssn, last_name, first_name, salary, d_id)
Department (dept_id, d_name)
Nested Query
Which employees have salaries greater than 'john' salary?
1. find what john earns, and then
2. find Who earns more than that johns amount.
Problem can be sloved by placing one query inside another
query
Sub Queries
The inner query or the subquery returns a value that is used
by the outer query or the main query.
A subquery is a SELECT statement that is within in a clause
of another SELECT statement.
Subquery is very useful when we need to select rows from a
table with a condition that depends on the data in the table
itself.
Nested Query
Syntax:
SELECT column_list FROM table WHERE expr operator
The subquery can be in a number of SQL clauses, including:
The FROM clause
The WHERE clause
The HAVING clause
INSERT, UPDATE, DELETE statements
Nested Query
Operator includes a comparison condition
Comparison conditions fall into two classes:
single-row operators (>, =, >=, <, <>, <=)
multiple-row operators (IN, ANY, ALL)
SELECT last_name FROM employees WHERE salary > (SELECT
salary FROM employees WHERE last_name = john);
Nested Query
Types of Subqueries
Single-row subqueries: Queries that return only one row
from the inner SELECT statement
Multiple-row subqueries: Queries that return more than
one row from the inner SELECT statement
There are also multiple-column Subqueries: Queries that
return more than one column from the inner SELECT
statement.
Nested Query - Guidelines
Enlcose subqueries in paranthesis
Place subqueries on right side of the comparison condition
Use single-row operators with single-row subqueries and
multiple-row operators with multiple-row subqueries
Single-row Subqueries
Query2: D isplay the employees who is working in the
department as same as that of employee 141.
Single-row Subqueries
Query2: D isplay the employees who is working in the
department as same as that of employee 141.
SELECT last_name, d_id FROM employees
WHERE d_id = (SELECT d_id FROM employees
WHERE ssn = 141);
Single-row Subqueries
Query 3: Display employees whose d_id is same as that of
employee 141 and whose salary is greater than that of employee
143.
Single-row Subqueries
Query 3: Display employees whose d_id is same as that of
employee 141 and whose salary is greater than that of employee
143.
SELECT last_name, d_id, salary FROM employees
WHERE d_id =
(SELECT d_id FROM employees WHERE ssn = 141)
AND
salary > (SELECT salary FROM employees WHERE
ssn = 143);
Using Group functions in Subquery
Query 4: Display the details of an employee whose salary is
minimum among all employees.
Using Group functions in Subquery
Query 4: Display the details of an employee whose salary is
minimum among all employees.
SELECT last_name, d_id, salary FROM employees
WHERE salary = (SELECT MIN(salary) FROM
employees);
Subquery in HAVING
Query 5: Display all the departments that have a minimum
salary greater than department 50.
Subquery in HAVING
Query 5: Display all the departments that have a minimum
salary greater than department 50.
Select d_id, MIN(salary) FROM employees
GROUP BY d_id
HAVING MIN(salary) > (SELECT MIN(salary) FROM
employees WHERE d_id = 50);
Multiple-row Subqueries
What is wrong in the following query?
SELECT ssn, last_name FROM employees WHERE
salary = (SELECT MIN(salary) FROM employees
GROUP BY d_id);
Inner query returns more than one row.
Use multiple-row comparison operators.
The multiple-row operator expects one or more values.
Multiple-row Subqueries
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
Employee
ssn
last_name first_name
100 Arun
Kumar
101 Ram
Varma
102 Rajesh
Kumar
103 Anil
Gupta
104 Shyam
Kant
105 Raghu
Raman
Department
dept_id d_name
10
20
Mkt
Sales
salary
d_id
3000
3400
4400
4300
4700
3000
10
10
20
10
20
20
Using IN
Query 6: Find the employees whose salary is equal to the
minimum salary in their department
Using IN
Query 6: Find the employees whose salary is equal to the
minimum salary in their department
SELECT ssn, last_name, salary, d_id FROM employees
WHERE salary IN (SELECT MIN(salary) FROM
employees GROUP BY d_id);
ssn
last_name salary
d_id
100
105
Arun
Raghu
10
20
3000
3000
Using ANY
Query 7: Find the employees whose salary is greater than or
equal to maximum salary for each department
select ssn, last_name, salary,d_id from employees where
salary >= ANY (select max(salary) from employees
group by d_id) --> (4300 , 4700)
ssn
last_name salary
d_id
102
103
104
Rajesh
Anil
Shyam
4400
4300
4700
20
10
20
Using ANY
Query 8: Find the employees whose salary is greater than the
maximum salary for each department
select ssn, last_name, salary,d_id employees from where
salary > ANY (select max (salary) from employees
group by d_id) --> (4300 , 4700)
ssn
last_name salary
d_id
102
Rajesh
4400
20
Using ALL
Query 9: Find the employees whose salary is greater than or
equal to maximum salary for each department
Select ssn,last_name,salary from employee where
salary >= ALL (select max(salary) from employee
group by d_id) --> (4300 , 4700)
ssn
last_name salary
d_id
104
Shyam
20
4700
Using ALL
Query 10: Find the employees whose salary is greater than the
maximum salary for each department
Select last_name,salary from employee where
salary > ALL (select max(salary) from employee group
by d_id) --> (4300 , 4700)
No rows selected
Correlated Sub Queries
Correlated subqueries are used for row-by-row processing.
Each subquery is executed once for every row of the outer
query.
A subquery references a column of a table present in the
outer query.
Nested Vs Correlated Sub Queries
Nested: the inner SELECT query runs first and executes
once, returning values to be used by the main query
Correlated: executes once for each candidate row
considered by the outer query. OR inner query is driven by
the outer query
Nested Vs Correlated Sub Queries
Nested:
1. the inner query executes first and finds a value
2. the outer query executes once, using the value from
the inner query
Correlated:
Get a candidate row (fetched by the outer query)
Execute the inner query using the value of the candidate
row
Use the values resulting from inner query to qualify or
disqualify the candidate
Repeat until no candidate row remains
Correlated Sub Queries
Syntax:
SELECT column1, column2, ....
FROM table1 outer
WHERE column1 operator
(SELECT column1, column2
FROM table2
WHERE expr1=outer.expr2);
The WHERE clause of subquery references a column of a
table present in the outer query.
Correlated Sub Queries
Query: Find all employees who earn more than the average
salary in their department:
SELECT l_name, salary, d_id
FROM employee outer
WHERE salary >
(SELECT AVG(salary)
FROM employees
WHERE d_id=outer.d_id);
Using SET operators
SET Operators
The SET operators combine the results of two or more
component queries into one result
Queries containing SET operators are called compound
queries
All SET operators have equal precedence
SET Operators
UNION : All distinct rows selected by either query
UNION ALL: All rows selected by either query, including
all duplicates
INTERSECT: All distinct rows selected by both queries
MINUS: All distinct rows that are selected by the first
SELECT statement and not selected in the second SELECT
statement
SET Operator
Employee
e_id
l_name
job_id
hire_date
100
101
102
Raja
Kumar
Ram
SA-REP 10-Jun-80
ST-MAN 14-Aug-84
SA-PRES 08-Apr-80
d_id
10
20
30
SET Operator
Job_history
e_id
start_date
end_date
job_id
d_id
100
101
101
100
10-Jun-80
14-Aug-84
04-Sep-85
29-Mar-82
28-Mar-82
03-Sep-85
30-Jun-87
18-Jan-88
SA-REP
ST-MAN
ST-CLK
SA-MGR
90
20
20
90
UNION SET Operator
The UNION operator returns all rows selected by either
query.
Returns all rows from multiple tables and eliminate any
duplicate rows
UNION: Guidelines
The number of columns and datatypes of the columns being
selected must be identical in all the SELECT statements used in
the query.
The names of columns need not be identical.
UNION operates over all of the columns being selected.
By default, output is sorted in ascending order of the first
column of SELECT clause.
UNION
Display the current and previous job details of all
employees. Display each employee only once.
SELECT e_id, job_id FROM employees
UNION
SELECT e_id, job_id FROM job_history;
e_id
100
100
101
101
102
job_id
SA-REP
SA-MGR
ST-MAN
ST-CLK
SA-PRES
5 rows
UNION
SELECT e_id, job_id,d_id FROM employees
UNION
SELECT e_id, job_id,d_id FROM job_history;
e_id
100
100
100
101
101
102
job_id
SA-REP
SA-REP
SA-MGR
ST-MAN
ST-CLK
SA-PRES
d_id
10
90
90
20
20
30
6 rows
UNION ALL
Duplicate rows are not eliminated
Output is not sorted by default
With the exception of above, guidelines for both UNION
and UNION ALL are the same
UNION ALL
Display the current and previous departments of all
employees.
SELECT e_id, job_id,d_id FROM employees
UNION ALL
SELECT e_id, job_id,d_id FROM job_history;
e_id job_id
d_id
100
100
100
101
101
101
102
SA-REP
SA-REP
SA-MGR
ST-MAN
ST-MAN
ST-CLK
SA-PRES
10
90
90
20
20
20
30
7 rows
INTERSECT
Display the employee Ids and job Ids of employees who
currently have a job title that they held before beginning
their tenure with the company
SELECT e_id, job_id FROM employees
INTERSECT
SELECT e_id, job_id FROM job_history;
e_id job_id
100 SA-REP
101 ST-MAN
2 rows
MINUS
Display the employee ID of those employees who have not
changed their jobs even once.
SELECT e_id, job_id,d_id FROM employees
MINUS
SELECT e_id, job_id,d_id FROM job_history;
e_id
103
job_id
SA-PRES
Thank You