OBJECTIVES

 Define terms
CHAPTER 7:  Write single and multiple table SQL queries
ADVANCED SQL  Define and use three types of joins

 Write noncorrelated and correlated

subqueries
 Understand and use SQL in procedural
languages (e.g. PHP, PL/SQL)
 Understand triggers and stored procedures

PROCESSING MULTIPLE TABLES PROCESSING MULTIPLE TABLES

 Join–a relational operation that causes two or more  Outer join–a join in which rows that do not have
tables with a common domain to be combined into a matching values in common columns are
single table or view nonetheless included in the result table (as
 Equi-join–a join in which the joining condition is opposed to inner join, in which rows must have
based on equality between values in the common matching values in order to appear in the result
columns; common columns appear redundantly in the table)
result table  Union join–includes all columns from each table
 Natural join–an equi-join in which one of the in the join, and an instance for each row of each
duplicate columns is eliminated in the result table table
The common columns in joined tables are usually the primary key  Self join–Matching rows of a table with other
of the dominant table and the foreign key of the dependent table in rows from the same table
1:M relationships
3 4
THE FOLLOWING SLIDES CREATE TABLES FOR
Figure 7-2 THIS ENTERPRISE DATA MODEL
Visualization of different join types with results returned
in shaded area
(from Chapter 1, Figure 1-3)

5 6

Figure 7-1 Pine Valley Furniture Company Customer_T and

Order_T tables with pointers from customers to their orders EQUI-JOIN EXAMPLE
 For each customer who placed an order, what is
the customer’s name and order number?

Customer ID
appears twice in the
result

These tables are used in queries that follow

7 8
EQUI-JOIN EXAMPLE – ALTERNATIVE NATURAL JOIN EXAMPLE
SYNTAX  For each customer who placed an order, what is the
customer’s name and order number?
Join involves multiple tables in FROM clause

INNER JOIN clause is an alternative to WHERE clause, and is

used to match primary and foreign keys.

An INNER join will only return rows from each table that have ON clause performs the equality Note: from Fig. 7-1, you see that only
matching rows in the other. 10 Customers have links with orders
check for common columns of the
two tables  Only 10 rows will be returned from
This query produces same results as previous equi-join example. this INNER join

9 10

OUTER JOIN EXAMPLE

 List the customer name, ID number, and order number Outer Join
for all customers. Include customer information even for Results
customers that do have an order.
Unlike
INNER join,
this will
include
customer
LEFT OUTER JOIN clause Unlike INNER join, this rows with no
causes customer data to will include customer matching
appear even if there is no rows with no matching order rows
corresponding order data order rows

11 12
MULTIPLE TABLE JOIN EXAMPLE Figure 7-4 Results from a four-table join (edited for readability)
 Assemble all information necessary to create an invoice for order
number 1006 From CUSTOMER_T table

Four tables
involved in
this join

From ORDER_T table From PRODUCT_T table

Each pair of tables requires an equality-check condition in the WHERE clause,
matching primary keys against foreign keys
13

14

SELF-JOIN EXAMPLE
Figure 7-5 Example of a self-join

The same table is

used on both sides
of the join;
distinguished using
table aliases

Self-joins are usually used on tables with unary relationships

15 16
PROCESSING MULTIPLE TABLES SUBQUERY EXAMPLE
USING SUBQUERIES  Show all customers who have placed an order
The IN operator will test to
 Subquery–placing an inner query (SELECT see if the CUSTOMER_ID
statement) inside an outer query value of a row is included in
 Options: the list returned from the
 In a condition of the WHERE clause subquery
 As a “table” of the FROM clause
 Within the HAVING clause
 Subqueries can be:
 Noncorrelated–executed once for the entire outer query Subquery is embedded in parentheses. In
 Correlated–executed once for each row returned by the this case it returns a list that will be used
outer query in the WHERE clause of the outer query

17 18

Figure 7-6 Graphical depiction of two ways to

JOIN VS. SUBQUERY answer a query with different types of joins
 Some queries could be accomplished by either a
join or a subquery

Join version

Subquery version

19 20
Figure 7-6 Graphical depiction of two ways to
answer a query with different types of joins CORRELATED VS. NONCORRELATED
SUBQUERIES
 Noncorrelated subqueries:
 Do not depend on data from the outer query
 Execute once for the entire outer query

 Correlated subqueries:
 Make use of data from the outer query
 Execute once for each row of the outer query
 Can use the EXISTS operator

21 22

Figure 7-8a Processing a noncorrelated subquery

CORRELATED SUBQUERY EXAMPLE
 Show all orders that include furniture finished in natural
ash
The EXISTS operator will return a
TRUE value if the subquery resulted
in a non-empty set, otherwise it
returns a FALSE

 A correlated subquery always refers to The subquery is testing

an attribute from a table referenced in for a value that comes
A noncorrelated subquery processes completely before the outer query begins the outer query from the outer query
23 24
Figure 7-8b ANOTHER SUBQUERY EXAMPLE
Processing a
 Show all products whose standard price is higher than
correlated Subquery refers to outer- the average price
subquery query data, so executes once One column of the subquery is an
for each row of outer query Subquery forms the derived aggregate function that has an alias
table used in the FROM clause name. That alias can then be
of the outer query
Note: only the referred to in the outer query
orders that
involve products
with Natural
Ash will be
included in the
final results

The WHERE clause normally cannot include aggregate functions, but because
the aggregate is performed in the subquery its result can be used in the outer
query’s WHERE clause

25 26

Figure 7-9 Combining queries using UNION

UNION QUERIES
 Combine the output (union of multiple queries)
together into a single result table
Note: with UNION
queries, the
quantity and data
First query types of the
attributes in the
SELECT clauses
Combine of both queries
must be identical

Second query

27 28
TIPS FOR DEVELOPING QUERIES QUERY EFFICIENCY CONSIDERATIONS
 Be familiar with the data model (entities and
relationships)  Instead of SELECT *, identify the specific
 Understand the desired results attributes in the SELECT clause; this helps
 Know the attributes desired in result reduce network traffic of result set
 Identify the entities that contain desired  Limit the number of subqueries; try to make
attributes
everything done in a single query if possible
 Review ERD
 If data is to be used many times, make a
 Construct a WHERE equality for each link
 Fine tune with GROUP BY and HAVING clauses
separate query and store it as a view
if needed
 Consider the effect on unusual data
29 30

GUIDELINES FOR BETTER QUERY GUIDELINES FOR BETTER QUERY

DESIGN DESIGN (CONT.)
 Understand how indexes are used in query
 Create temporary tables for groups of queries
processing
 Combine update operations
 Keep optimizer statistics up-to-date
 Retrieve only the data you need
 Use compatible data types for fields and literals
 Don’t have the DBMS sort without an index
 Write simple queries
 Learn!
 Break complex queries into multiple simple parts
 Consider the total query processing time for ad hoc
 Don’t nest one query inside another query
queries
 Don’t combine a query with itself (if possible avoid
self-joins)
31 32
Figure 7-10 An SQL Transaction sequence (in pseudocode)
ENSURING TRANSACTION INTEGRITY
 Transaction = A discrete unit of work that must
be completely processed or not processed at all
 May involve multiple updates
 If any update fails, then all other updates must be
cancelled
 SQL commands for transactions
 BEGIN TRANSACTION/END TRANSACTION
 Marks boundaries of a transaction
 COMMIT
 Makes all updates permanent
 ROLLBACK
 Cancels updates since the last COMMIT

33 34

DATA DICTIONARY FACILITIES TRIGGERS AND ROUTINES

 System tables that store metadata
 Users usually can view some of these tables  Triggers–routines that execute in
 Users are restricted from updating them response to a database event (INSERT,
 Some examples in Oracle 11g UPDATE, or DELETE)
 Routines
 Program modules that execute on demand
 Functions–routines that return values and
take input parameters
 Procedures–routines that do not return
values and can take input or output
parameters
35 36
Figure 7-11 Triggers contrasted with stored procedures (based on
Mullins 1995) Figure 7-12 Trigger syntax in SQL:2008
Procedures are called explicitly

Figure 7-13 Syntax for creating a routine, SQL:2008

Source: adapted from Mullins, 1995.

Triggers are event-driven

37 38

EMBEDDED AND DYNAMIC SQL

 Embedded SQL
 Includinghard-coded SQL statements in a
program written in another language such as
C or Java
 Dynamic SQL
 Ability
for an application program to generate
SQL code on the fly, as the application is
running

39 40
REASONS TO EMBED SQL IN 3GL

 Can create a more flexible, accessible

interface for the user
 Possible performance improvement

 Database security improvement; grant

access only to the application instead of
users

41