SQL Hacks

Write queries that won’t turn to digital dust

Andrew Cumming is a big fan of SQL; he says it’s elegant, well
designed, and phenomenally useful…but it’s not always easy to use:
“You can do incredibly powerful queries in just a few lines of code, but then
other times you have to tie yourself in knots just to get something trivial
done.” With coauthor Gordon Russell, Cumming has written “SQL Hacks” (O’Reilly),
a collection of 100 tips and tools to help users get more from SQL’s power and
flexibility, and spend less time tying themselves in knots. “There are a handful
of SQL ‘cliches’ or ‘phrases’ that I use all the time, but not everyone knows
about them,” says Cumming. “They save a lot of time and once they get under your
skin, you can use them without thinking about them.”

For example, “Just as you might use for (int i=0;i<10;i++) without thinking
about how it works, I use SELECT 100*foo/(SELECT SUM(foo) FROM
bar) FROM bar or a more efficient variation."

"SQL Hacks" is primarily aimed at the working programmer, but Cumming assures
non-programmers that they'll find the book useful as well. "If you're confident
using a drag-and-drop query builder to get the data you need on an ad-hoc basis,
then maybe you should think about taking your skills to the next level. You can
use a query builder for the simple stuff–it's ideal–but if you're asking
complex questions of your database then sooner or later you need to get your
hands dirty and write some SQL."

The book is also for those programmers who hate SQL, the kind who reach for a
loop or hash table to do anything but the simplest filtering commands. "If they
took just a little time to learn a handful of tricks, they could improve their
code and their productivity immensely," says Cumming.

Equally applicable for users running Access, MySQL, SQL Server, Oracle, or
PostgreSQL, "SQL Hacks" shows how to:

-Wrangle data in the most efficient way possible -Aggregate and organize data
for meaningful and accurate reporting -Make the most of subqueries, joins, and
unions -Stay on top of the performance of your queries and the server that runs
them -Avoid common SQL security pitfalls, including the dreaded SQL injection
attack

"Just a tiny bit more SQL can make a huge difference in terms of efficiency and
maintainability," Cumming reminds his readers. "If you have a choice between
implementing a complicated calculation in the program or in the database, many
programmers will do it in the program because that's the language they're more
comfortable with. But if you do it in the database, the chances are it will run
faster. Not only that, it will live longer, too. When the next version of your
application comes out, the application code may well be digital dust–but the
data, the database, and your queries and views are likely to survive–to be
reused over and over."