Transcript lecture 14

DDL – subquery
Sen Zhang
1
Objectives
• What is a subquery?
• Learn how to create nested SQL queries
• Read sample scripts and book for different kinds of subquery.
2
What is a subquery?
• A subquery is a standard select query that is nested within a
select, update, insert, or delete command.
• Used to select results based on the result of a query
• A subquery can even contain subqueries within itself, i.e.
subqueries can be nested to more than one level (nested
subqueries). Theoretically speaking, you can nest subqueries to
an infinite number of levels of depth.
3
When is a subquery needed?
• When a certain information that is not directly available from any
physical table must be answered using a select statement
before a larger question can be addressed, we need a subquery.
• For example, to write a select statement to find out whose salary
is above the average, you must first find out what “average
salary” is, which itself requires a select statement already.
4
Which query will be evaluated first?
• Inner subquery will be evaluated first, then outer query.
5
What kind of results do queries return?
• The result of a subquery could be
–
–
–
–
a table of one row and one column, thus a scalar value,
a table of one single column, thus a list of item
a table of a single row of one single record, thus a tuple
a table of multiple columns and multiple records, thus a relation
6
Which clauses does a subquery can
appear in?
• Depending on the evaluating result of a subquery, the subquery
can be used to provide data for different clauses
– From
– Where
– Having
7
How to write a subquery?
• put a select statement in a pair of parenthesis.
• It can be used as a value, a list, or a table generated on the fly
8
Creating Nested Queries
• Consists of a main query and one or more subqueries.
– Main query: first query that appears in the SELECT command
– Subquery retrieves values that the main query’s search condition
must match
9
Creating Nested Queries
• Being aware of what will be returned by a nested query is very
important.
• Nested queries can return single or multiple values
– To match single values use = operator
– To match multiple values use IN operator
10
Nested Subquery Example
11
Aliases & In/Subqueries
• Let us find out average salary of each individual department
• SELECT avg(salary), dno from employee group by dno;
• This gives:
AVG(SALARY)
DNO
----------- ---------53766.75
1
88447
4
101508.5
5
12
• Notice that, in the previous slide, the “min(salary)" appears the
Select lines as the column header on the report.
• Avg(salary) may not be a good field name used by outerquery,
therefore we can assign alias name to avg(salary) as following
• SELECT avg(salary) avgsalary, dno from employee group by
dno;
• This gives:
AVGSALARY
DNO
---------- ---------53766.75
1
88447
4
101508.5
5
13
select
lname, fname, t1.salary, ssn, t1.dno
from
employee t1,
(select min(salary) avgsalary, dno from employee group by dno)
t2
where
t1.dno=t2.dno and
t1.salary=t2.avgsalary;
14
Correlated subquery
• A correlated subquery is a subquery that uses values from the
outer query.
• With a correlated subquery, the database must run the subquery
for each evaluation because it is based on the outer query’s
data.
• The outer query knows nothing about the inner query except its
results. For that reason, the outer query cannot reference any
columns in the subquery. However, the subquery has access to
the outer query and can reference outer query columns, thus the
correlated subquery.
15
An correlated nested query
• Retrieve the name of each employee who has a dependent with
the same first name and same sex as the employee.
• Select e.fname, e.lname
from employee e
where e.ssn in (select essn
from dependent
where e.fname=dependent_name
and e.sex=sex);
16
• In this example, the subquery references the sex in the outer
query. The value of the sex changes by row of the outer query,
so the database must rerun the subquery for each row
comparison.
• This has a significant performance impact on the execution time
of the query, and for that reason, correlated subqueries should
be avoided if possible.
17
Subquery and join
• In general, a query written with nested select-from-where blocks
and using the = or IN comparsion operators can always be
expressed as a single block query.
• For example, the previous correlated query can be rewritten as
– Select e.fname, e.lname
From employee e, dependent d
where e.ssn=d.essn and e.sex=d.sex and
e.fname=d.dependent_name;
18
• Nested subqueries are slower than joins and should be used
sparingly, so if you can use join to solve the problem, probably
you do not want to use subquery solution.
• A nested query may or may not have a equivalent flat joining
query solution.
19
SQL - Subquery Inserts
•
Subqueries can be used to pull old data from your database and insert
it into new tables. (We have homework problem for it.)
•
For instance if we opened up a third store and we wanted to place the
same manager over 3 stores we could do this by pulling the manager's
information using a subquery and then inserting the records. Also note
that this form of insert will insert all cases where the subquery is true,
therefore several rows may or may not be inserted depending upon
how your table is set up.
SQL Code:
•
– INSERT INTO employees3 (id,Lastname,Firstname,Title)
(SELECT id,Lastname,Firstname,Title FROM employees WHERE
Title='manager');
20
The only limitation is one’s imagination.
• With complete mastery of a subqueries you can now see the
power of the SQL language.
• The SQL language is capable of nearly all things imaginable.
• Please read the sample script discussed in class and read book
for more query examples.
21