How to think in SQL?

Question

How do I stop thinking every query in terms of cursors, procedures and functions and start using SQL as it should be? Do we make the transition to thinking in SQL just by practise or is there any magic to learning the set based query language? What did you do to make the transition?

Answer 1

A few examples of what should come to your mind first if you're real SQL geek:

• Bible concordance is a FULLTEXT index to the Bible

• Luca Pacioli's Summa de arithmetica which describes double-entry bookkeeping is in fact a normalized database schema

• When Xerxes I counted his army by walling an area that 10,000 of his men occupied and then marching the other men through this enclosure, he used HASH AGGREGATE method.

• The House That Jack Built should be rewritten using a self-join.

• The Twelve Days of Christmas should be rewritten using a self-join and a ROWNUM

• There Was An Old Woman Who Swallowed a Fly should be rewritten using CTE's

• If the European Union were called European Union All, we would see 27 spellings for the word euro on a Euro banknote, instead of 2.

And finally you can read a lame article in my blog on how I stopped worrying and learned to love SQL (I almost forgot I wrote it):

• Click

And one more article just on the subject:

• Double-thinking in SQL

Answer 2

The key thing is you're manipulating SETS & elements of sets; and relating different sets (and corresponding elements) together. That's really the heart of it, imho. That's why every table should have a primary key; why you see set operators in the language; and why set operators like UNION won't (by defualt) return duplicate rows.

Of course in practice, the rules of sets are bent or broken but it's not that hard to see when this is necessary (otherwise, SQL would be TOO limited). Imho, just crack open your discrete math book and reacquaint yourself with some set exercises.