Advanced SQL

1
Chapter 8
Advanced SQL Database Systems Design,
Implementation, and Management, Seventh Edition,
Rob and Coronel
2
In this chapter, you will learn

• About the relational set operators UNION, UNION
  ALL, INTERSECT, and MINUS
• How to use the advanced SQL JOIN operator syntax
• About the different types of subqueries and
  correlated queries

3
(No Transcript)
4
Relational Set Operators

• UNION
• INTERSECT
• MINUS
• Work properly if relations are union-compatible
• Names of relation attributes must be the same and
  their data types must be identical

5
(No Transcript)
6
UNION

• Example -1
• SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL,
  CUS_AREACODE, CUS_PHONE
• FROM CUSTOMER
• UNION
• SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL,
  CUS_AREACODE, CUS_PHONE
• FROM CUSTOMER_2
• This example generates a combined listing of
  customersone that excludes duplicate records
• Example -2
• SELECT column-list FROM T1
• UNION
• SELECT column-list FROM T2
• UNION
• SELECT column-list FROM T3

7
UNION (continued)
8
UNION ALL

• UNION ALL query can be used to produce a relation
  that retains the duplicate rows
• UNION ALL statement can be used to unite more
  than just two queries
• Example query
• SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL,
  CUS_AREACODE, CUS_PHONEFROM CUSTOMERUNION
  ALLSELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL,
  CUS_AREACODE, CUS_PHONEFROM CUSTOMER_2

9
UNION ALL (continued)
10
INTERSECT

• The NTERSECT statement can be used to combine
  rows from two queries, returning only the rows
  that appear in both sets

11
MINUS

• The MINUS statement in SQL combines rows from two
  queries and returns only the rows that appear in
  the first set but not in the second

12
Syntax Alternatives

• For example, the following query returns the
  customer codes for all customers who are located
  in area code 615 and who have made purchases. (If
  a customer has made a purchase, there must be an
  invoice record for that customer.)
• SELECT CUS_CODE FROM CUSTOMER WHERE CUS_AREACODE
  '615'
• INTERSECT
• SELECT DISTINCT CUS_CODE FROM INVOICE

13
Syntax Alternatives (continued)

• For example, the following query returns the
  customer codes for all customers located in area
  code 615 minus the ones who have made purchases,
  leaving the customers in area code 615 who have
  not made purchases.
• SELECT CUS_CODE FROM CUSTOMER WHERE CUS_AREACODE
  '615'
• MINUS
• SELECT DISTINCT CUS_CODE FROM INVOICE

14
Tue 18-6 SQL Join Operators
15
Cross Join

• Syntax
• SELECT column-list FROM table1 CROSS JOIN table2
• Returns the Cartesian product of table1 and
  table2(old style).

PlayerName DepartmentId Scores DepartmentId DepartmentName
Jason 1 3000 1 IT
Irene 1 1500 1 IT
Jane 2 1000 1 IT
David 2 2500 1 IT
Paul 3 2000 1 IT
James 3 2000 1 IT
Jason 1 3000 2 Marketing
Irene 1 1500 2 Marketing
Jane 2 1000 2 Marketing
David 2 2500 2 Marketing
Paul 3 2000 2 Marketing
James 3 3000 2 Marketing
Jason 1 3000 3 HR
Irene 1 1500 3 HR
Jane 2 1000 3 HR
David 2 2500 3 HR
Paul 3 2000 3 HR
James 3 3000 3 HR
EXAMPLE SELECT FROM GameScores CROSS JOIN
Departments
PlayerName DepartmentId Scores
Jason 1 3000
Irene 1 1500
Jane 2 1000
David 2 2500
Paul 3 2000
James 3 2000
DepartmentId DepartmentName
1 IT
2 Marketing
3 HR
16
Natural Join
17
JOIN USING Clause
18
JOIN ON Clause
19
Outer Joins

• Returns not only matching rows, but also rows
  with unmatched attribute values for one table or
  both tables to be joined
• Three types
• Left
• Right
• Full

20
Outer Joins (continued)
21
Outer Joins (continued)
22
Outer Joins (continued)
23
Subqueries and Correlated Queries
24
Correlated Subqueries (continued)
Example (2) you want to know the vendor code and
name of vendors for products having a quantity on
hand that is less than double the minimum quantity
Example (1) you want to know all customers who
have placed an order lately