Chapter 12: When One Query Depends Upon Another

1
Chapter 12 When One Query Depends Upon Another

• Correlated Subqueries. Subqueries in the where
  clause.
• Anti-Joins. Using NOT IN or NOT EXISTS with a
  subquery.
• Outer Joins. Special type of join between tables
  where matching data not found.
• Union, Intersect, Minus. Ways of combining result
  sets from separate queries together.

2
Chapter 12 When One Query Depends Upon Another

• Regular Joins, Review.Example from p. 242, a 3
  table equi-join
• SELECT W.Name, W.Lodging
• FROM Worker W, Workerskill WS, Lodging L
• WHERE W.Name WS.Name
• and W.Lodging L.Lodging
• and Skill COMBINE DRIVER
• and Address LIKE EDMESTON

3
Chapter 12 When One Query Depends Upon Another

• Correlated Subqueries.
• A correlated subquery is a query in which the
  subquery refers to values in the parent query.
• A correlated subquery can return the same results
  as a join, but can be used where a join cannot
• update, insert and delete statements.
• Group by queries
• Example from book, p.245

4
Chapter 12 When One Query Depends Upon Another

• Another Corrolated Example
• The following statement returns data about
  employees whose salaries exceed the averages for
  their departments. The following statement
  assigns an alias to EMP, the table containing the
  salary information, and then uses the alias in a
  correlated subquery
• SELECT deptno, ename, sal
• FROM emp x
• WHERE sal gt (SELECT AVG(sal)
• FROM emp
• WHERE x.deptno deptno)
• ORDER BY deptno
• For each row of the EMP table, the parent query
  uses the correlated subquery to compute the
  average salary for members of the same
  department. The correlated subquery performs
  these steps for each row of the EMP table
• 1. The DEPTNO of the row is determined.
• 2. The DEPTNO is then used to evaluate the
  parent query.
• 3. If that rows salary is greater than the
  average salary for that rows department, then
  the row is returned.
• The subquery is evaluated once for each row of
  the EMP table.

5
Chapter 12 When One Query Depends Upon Another

• Exists
• The EXISTS keyword is similar IN.
• EXISTS tests for the existence of any row. Unlike
  IN however, EXISTS does not match columns and it
  usually only makes sense to use with a correlated
  subquery.
• Example from p. 250
• select Name, Skill
• from WORKERSKILL
• where EXISTS (
• select Name from WORKERSKILL
  group by Name
• having COUNT(Skill) gt 1)

6
Chapter 12 When One Query Depends Upon Another

• Not Exists
• very similar to NOT IN
• Much faster! Not exists can use indexes.
• Example from p. 255
• select S.Skill
• from SKILL S
• where NOT EXISTS (
• select whatever from
  WORKERSKILL WS where WS.Skill S.Skill)
• Equivalent NOT IN query
• select S.Skill
• from SKILL S
• where S.Skill NOT IN (
• select WS.SKILL from
  WORKERSKILL WS)

7
Chapter 12 When One Query Depends Upon Another

• Outer Join
• The outer join extends the result of a simple
  join. An outer join returns all rows that satisfy
  the join condition and those rows from one table
  for which no rows from the other satisfy the join
  condition. Such rows are not returned by a simple
  join.
• To write a query that performs an outer join of
  tables A and B and returns all rows from A, apply
  the outer join operator () to all columns of B
  in the join condition.
• For all rows in A that have no matching rows in
  B, Oracle returns NULL for any select list
  expressions containing columns of B.
• This is the basic syntax of an outer join of two
  tables
• SELECT table1.column
• FROM table1, table2
• WHERE table1.column table2.column()

8
Chapter 12 When One Query Depends Upon Another

• Outer Join Example
• You want a list of all workers and their skills.
  If a worker has no skills, you want him listed
  without corresponding skills.
• Equi-join query
• NAME SKILL
• ------------------------- ------------------------
  -
• ADAH TALBOT WORK
• DICK JONES SMITHY
• ELBERT TALBOT DISCUS
• HELEN BRANDT COMBINE DRIVER
• JOHN PEARSON COMBINE DRIVER
• JOHN PEARSON WOODCUTTER
• JOHN PEARSON SMITHY
• VICTORIA LYNN SMITHY
• WILFRED LOWELL WORK
• WILFRED LOWELL DISCUS
• Outer Join Query
• NAME SKILL
• ------------------------- ------------------------
  -

SELECT W.Name, S.Skill FROM Worker W,
WorkerSkill S WHERE W.Name S.Name
SELECT W.Name, S.Skill FROM Worker W,
WorkerSkill S WHERE W.Name S.Name()
9
Chapter 12 When One Query Depends Upon Another

• Outer Join
• Outer join queries are subject to the following
  rules and restrictions
• The () operator can only appear in the WHERE
  clause, not in the select list, and can only be
  applied to a column of a table or view.
• If A and B are joined by multiple join
  conditions, the () operator must be used in all
  of these conditions.
• The () operator can only be applied to a column,
  rather than to an arbitrary expression, although
  an arbitrary expression can contain a column
  marked with the () operator.
• A condition containing the () operator cannot be
  combined with another condition using the OR
  logical operator.
• A condition cannot use the IN comparison operator
  to compare a column marked with the () operator
  to another expression.
• A condition cannot compare a column marked with
  the () operator to a subquery.
• If the WHERE clause contains a condition that
  compares a column from table B to a constant, the
  () operator must be applied to the column so
  that the rows from table A for which Oracle has
  generated NULLs for this column are returned.
• In a query that performs outer joins of more than
  two pairs of tables, a single table can only be
  the NULLgenerated table for one other table. For
  this reason, you cannot apply the () operator to
  columns of B in the join condition for A and B
  and the join condition for B and C.

10
Chapter 12 When One Query Depends Upon Another

• Replacing NOT IN with an Outer Join
• Example similar to p. 253
• Using NOT IN
• select A.Name, A.Lodging from Worker A
  where A.Name NOT IN (select B.Name
  from WORKERSKILL B where B.skill
  SMITHY)
• Using Outer Join
• select A.Name, A.Lodging from Worker A,
  WORKERSKILL B
• where A.Name B.Name()
• and B.Name IS NULL
• and B.Skill() SMITHY

11
Chapter 12 When One Query Depends Upon Another

• Set Operators
• UNION returns distinct rows for the combination
  of two select statements.
• UNION ALL returns all rows for the combination of
  two select statements regardless of
  duplication.
• INTERSECT returns distinct rows for the
  combination of two select statements where data
  matches.
• MINUS return the rows from one select statement
  excluding the rows of a second select statement.

12
Chapter 12 When One Query Depends Upon Another

• Set Operations Example
• An obvious exampleselect Name from Longtime
  minusselect Name from Prospect
• A subtle exampleselect Name, Lodging from
  Longtime minusselect Name, Address from
  Prospect
• Columns must be compatible for set operations,
  but not necessarily the same.

13
Chapter 13 Complex Possibilities

• Creating Complex Views. View of a group, view of
  a total, combined view.pp. 268-273
• FROM Clause Subqueries. Essentially temporary
  views. pp 273-4
• Temporary Tables, ROLLUP, GROUPING, CUBE. New
  Oracle8i features. pp. 275-9
• Hierarchical Queries. START WITH and CONNECT BY
  keywords. pp. 279-288

14
Chapter 13 Complex Possibilities

• FROM Clause Subqueries.
• New feature as of Oracle 7.3.
• Works just like a view.
• Example similar to p. 274
• SELECT L1.Person, L1.Amount, 100L1.Amount/T.Tota
  l
• FROM Ledger L1,(Select SUM(Amount) Total from
  Ledger
• where Action IN (BOUGHT,PAID)) T
• WHERE Action IN (BOUGHT,PAID)

15
Chapter 17 DECODE

• Decode is Oracle non-standard SQL. Extremely
  powerful, yet underutilized.
• Decode works similar to if/then/else
• DECODE (value, if1, then1, if2, then2,... ,
  default)
• Common uses illustrated in the text
• Aggregating groups of data into a single column.
  Example, p. 353
• Flip a table on its side, ie rows become columns.
  Example, p. 358
• Dividing data into sections based on row number.
  This is done in conjunction with the MOD
  function. Example, p. 359
• Complex computations that require if/then logic.
  Example, p. 362