Question

I have just searched through StackOverflow but couldn't find much...

To all the SQL experts,

is there anything comparable to the "design patterns movement" which has taken place in the programming language world since 1994? You know, the "Gang of Four" book and the like.

I'm asking because I feel the need to structure/organize typical SQL constructs for educational purposes. The question is focussing primarily on querying, not on data modeling.

These possible patterns might classify

• when to use a correlated subquery vs. a join,
• when to use a derived table,
• when to use an union vs. a disjunction

and the like. Such rules of thumb may be something like

• If you want to get the rows with the maximal column value c of all rows in table T, use a join on a derived query with group by max as select T.* from T inner join (select ...)...

(I don't claim this is a valid solution, it's just the way I would like a sample to be.)

And I expect them to be general approaches though the possible solutions will obvioulsy depend on the features of the SQL engine - i.e. are WINDOW functions available). In that respect, I would prefer solutions working with SA 11.0.1 and above:)

Any hints are highly appreciated!

---

[Just do clarify: Inspite of the heavy usage of "like" and "pattern" in this question, I am not at all refering to pattern matching:)]

Answer 1

In a word, the answer is "yes" though I think many references merely scratch the surface. As one example, a relatively new book by Vadim Tropashko offers solutions to the following 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

but as useful as these patterns may be, to me these are merely a part of the problem. In my view, "Design Patterns" with relational databases must include both logical and physical schema design since there are always tradeoffs between the SQL constructions one might use and aspects of the physical schema that render the queries (or updates) possible (or not), and what their performance characteristics may be.