SQL Design Patterns
Advanced SQL programming
idioms
Genesis
C++ world
Advanced C++ Programming Styles and Idioms, by
James O. Coplien
Design Patterns: Elements of Reusable Object-
Oriented Software by Erich Gamma et al
SQL
SQL for Smarties by Joe Celko
SQL Cookbook by Anthony Molinaro
The Art of SQL
by Stephane Faroult, Peter Robson
What is a SQL Pattern?
A common design vocabulary
A documentation and learning aid
An adjunct to existing design methods
A target for refactoring
Large range of granularity -- from very
general design principles to language-
specific idioms
List of Patterns
Counting
Conditional summation
Integer generator
String/Collection decomposition
List Aggregate
Enumerating pairs
Enumerating sets
Interval coalesce
Discrete interval sampling
User-defined aggregate
Pivot
Symmetric difference
Histogram
Skyline query
Relational division
Outer union
Complex constraint
Nested intervals
Transitive closure
Hierarchical total
Symmetric Difference
A=B ?
Isn’t it Equality operator ?
Venn diagram
B\A
A∩B
A\B
(A \ B) ∪ (B \ A)
(A ∪ B) \ (A ∩ B)
SQL Query
(
select * from A
minus
select * from B
) union all (
select * from B
minus
select * from A
)
Test
create table A as
select obj# id, name from
[Link]$
where rownum < 100000;
create table B as
select obj# id, name from
[Link]$
where rownum < 100010;
Execution Statistics
Anti Join Transformation
convert_set_to_join = true:
select * from A
where (col1,col2,…) not in (select
col1,col2,… from B)
union all
select * from B
where (col1,col2,…) not in (select
col1,col2,… from A)
Execution Statistics
Optimization continued…
CREATE INDEX A_id_name ON A(id, name);
CREATE INDEX B_id_name ON B(id, name);
_hash_join_enabled = false
_optimizer_sortmerge_join_enabled =
false
or
/*+ use_nl(@"SEL$74086987" A)
use_nl(@"SET$D8486D66" B)*/
Symmetric Difference via
Aggregation
select * from (
select id, name,
sum(case when src=1 then 1 else 0
end) cnt1,
sum(case when src=2 then 1 else 0
end) cnt2
from (
select id, name, 1 src from A
union all
select id, name, 2 src from B
) group by id, name
)where cnt1 <> cnt2
Execution Statistics
Equality checking via Aggregation
1. Is there any difference? (Boolean).
2. What are the rows that one table contains,
and the other doesn't?
|| orahash 512259
+
|| orahash 334382
+
|| orahash 592731
|| orahash +267629
=
1523431
Relational Division
ApplicantSkills
Name Language
JobApplicants
JobRequirements Steve SQL
Name
Language Pete Java
Steve
Pete
x SQL = Kate SQL
Java Steve Java
Kate
Pete SQL
Kate Java
Dividend, Divisor and Quotient
ApplicantSkills
Name Language
SQL JobRequirements
Steve
Language Name
Pete Java / SQL = ?
Kate
Kate SQL
Java
Kate Java
Remainder
Is it a common Pattern?
Not a basic operator in RA or SQL
Informally:
“Find job applicants who meet all job
requirements”
compare with:
“Find job applicants who meet at least one
job requirement”
Set Union Query
Given a set of sets, Sets
e.g {{1,3,5},{3,4,5},{5,6}} ID ELEMENT
1 1
Find their union: 1 3
1 5
SELECT DISTINCT element 2 3
FROM Sets 2 4
2 5
3 5
3 6
Set Intersection
Given a set of sets, Sets
e.g {{1,3,5},{3,4,5},{5,6}} ID ELEMENT
1 1
Find their intersection? 1 3
1 5
2 3
2 4
2 5
3 5
3 6
It’s Relational Division Query!
“Find Elements which belong to all sets”
compare with:
“Find Elements who belong to at least one
set”
ID ELEMENT
ID
1 1
1 3 ELEMENT
1
1
2
5
3
/ =
2 5
2 4
2 5 3
3 5
3 6
Implementation (1)
πName(ApplicantSkills) x JobRequirements
Name Language
Steve SQL
Pete Java
Kate SQL
Steve Java
Pete SQL
Kate Java
Implementation (2)
Applicants who are not qualified:
πName (
πName(ApplicantSkills) x JobRequirements
- ApplicantSkills
)
Implementation (3)
Final Query:
πName (ApplicantSkills) -
πName ( ApplicantSkills -
πName(ApplicantSkills) x JobRequirements
)
Implementation in SQL (1)
select distinct Name from ApplicantSkills
minus
select Name from (
select Name, Language from (
select Name from ApplicantSkills
), (
select Language from JobRequirements
)
minus
select Name, Language from ApplicantSkills
)
Implementation in SQL (2)
select distinct Name from ApplicantSkills i
where not exists (
select * from JobRequirements ii
where not exists (
select * from ApplicantSkills iii
where [Link] = [Link]
and [Link] = [Link]
)
)
Implementation in SQL (3)
“Name the applicants such that for all job
requirements there exists a corresponding entry
in the applicant skills”
“Name the applicants such that there is no job
requirement such that there doesn’t exists a
corresponding entry in the applicant skills”
“Name the applicants for which the set of all job
skills is a subset of their skills”
Implementation in SQL (4)
select distinct Name from ApplicantSkills i
where
(select Language from JobRequirements ii
where [Link] = [Link])
in
(select Language from ApplicantSkills)
Implementation in SQL (5)
A⊆B A\B=∅
select distinct Name from ApplicantSkills i
where not exists (
select Language from ApplicantSkills
minus
select Language from JobRequirements ii
where [Link] = [Link]
)
Implementation in SQL (6)
select Name from ApplicantSkills s,
JobRequirements r
where [Link] = [Link]
group by Name
having count(*) = (select count(*) from
JobRequirements)
Book