Like many developers, I’ve bounced between various relational databases on my coding projects, primarly DB2, SQL Server and Oracle. If someone asked me if the SQL I write is compatible with all three, I’d sheepishly admit that most of it is.
Sheepishly, because generic, cross-platform SQL tends to be inefficient SQL. You can get away with it when working with modest amounts of data and users, but it’s still lazy, shoddy work.
Although not obvious from its title, SQL Cookbook is actually a great way to hone your SQL-writing skills, taking advantage of the powerful, vendor-specific features available in each major RDBMS brand. (The specific types of RDBMS covered by the book are DB2, SQL Server, Oracle, MySQL and PostgreSQL.)
The book’s format is to present a problem, such as creating a pivot table or generating a delimited list, then provide one or two solutions to the problem for each type of RDBMS. Sometimes the same solution works for multiple RDBMS, but in most cases a particular RDBMS has proprietary functions that result in a shorter or faster piece of SQL.
A notable example are “window functions” (also known as “analytic functions” in Oracle). They are a relatively new technology, having become an ISO SQL standard in 2003, but all recent versions of the major RDBMS products implement them to some extent. An example from Oracle is the following, which returns each employee’s department and the # of employees in that department.
select ename, deptno, count(*) over (partition by deptno) as cnt from emp
The generic/lazy way to implement that is with a subquery or joined query that returns the number of employees in each department, such as:
select e.ename, e.deptno, (select * from emp where deptno = e.deptno) as cnt from (select ename, deptno from emp)
The latter approach works, but it’s more complex and less efficient than using a window function.
If you’ve been writing SQL for more than 10 years, you learned to do it the long way and probably haven’t been forced to change your ways since. To learn about window functions, Appendix A of SQL Cookbook is a great place to start.
Another example is Oracle’s ability to return an object from a subquery. I had always thought it was a cardinal rule (and major pain-in-the-ass) of SQL: subqueries can return only 1 field. I couldn’t tell you how many times I’ve pasted multiple copies of the same subquery into a SQL statement because I needed to return a few different fields from it. I knew that it would hurt performance, but what option did I have?
It turns out that, when using Oracle, an option is to return those fields in an object. For example, the following query returns a couple of fields from the department table for each employee:
select
x.deptno,
x.ename,
x.multival.val1 dname,
x.multival.val2 loc,
x.multival.val3 today
from
(select e.deptno, e.ename, e.sal,
(select generic_obj(d.dname,d.loc,sysdate+1)
from dept d
where e.deptno=d.deptno) multival
from emp e
) x
I also liked the format of SQL Cookbook. As the title suggests, one way of using the book is as a reference. If stuck on a SQL coding problem, you can run through the table of contents, looking for a problem description that is similar to your own.
However, I found it worthwhile to read through the book chapter by chapter. Every piece of SQL is accompanied by a detailed but easy-to-follow narrative describing how and why it works. By reading these sections you’re sure to add a lot of new tools to your toolbox, even if you don’t need to solve the specific problem covered by that “recipe”.
Also, since the book separates the solutions by RDBMS, you’ll probably find that you can skip at least 50% of the book (for now), focusing only on the one or two RDBMS systems that you are currently working with. It’s refreshing to come across a software development book that can realistically be read in a week.
With the emergence of alternative ways of interfacing with databases, like LINQ, CouchDB and the various technologies gathering under the “NoSQL” banner, the day might come when crafting SQL statements is a dying art. A parallel might be drawn between SQL today and assembly language in the past — increasingly less important as higher level languages and the underlying hardware grow stronger. However, if this happens the remaining “artists” who’ve mastered SQL will be much in demand. This book is a good step towards raising your SQL to a higher level — from Betty Crocker to Julia Child, if you will.