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

Thinking in SQL

Answer 2

The way that I learned was by doing a lot of queries, and working at a job that required you to think in terms of result sets.

From your question, it seems like you've been writing lots of front-end code that uses sequential/procedural/iterative data manipulation. If you don't get on any projects that require you to use result set skills, I personally wouldn't worry about it.

One thing you might want to try is by trying to write analytical queries, e.g., generating simplistic reports on your data. In those cases you are trying to summarize large amounts of data by cordoning them off into sets.

Another good way would be to read a book on the theoretical/mathematical foundations to RDBMSes. Those deal strictly with set theory and how parts of the SQL query syntax relate directly with the math behind it. Of course, this requires you to like math. :)

Answer 3

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 4

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.

Answer 5

I found that the Art Of SQL was a useful kick in the head for getting into the right mindset.

Part of this, however, comes down to style. Obviously, you need to start thinking in result sets and not just procedurally. However, once you've start that, you will often find decisions have to be made.

Do you write the incredibly complex update statement that may be difficult to understand by anyone but yourself, and difficult to maintain, or do you write a less efficient, but easier to manage procedure?

I would HIGHLY suggest that you remember that SQL statements can have comments in them to clarifiy what they are doing, not just stored procedures.

link: The Art Of SQL

Answer 6

When people ask me about joins I send them here it has a great visual representation on what they are!

Answer 7

Best advice I can give you is that every time you think about processing something row-by-row, that you stop and ask yourself if there is a set-based way to do this.

Answer 8

One exercise you might want to try is this:

Take some of your existing reporting code from your application layer, preferably something that produces a single, tabular data set. Starting with the most basic elements, port it over to an SQL View.

Take all of the columns pulled from a single table and write the SQL statement to select that data. Then join on one table at a time and start figuring out the appropriate conditions and logic for your output.

You might come up against some particular task that at first seems impossible in SQL, but depending on the implementation you are programming against, there is almost always a way to get the result you're looking for. Check the documentation for your SQL implementation, or try Google.

This exercise has the benefit of giving you an original report to test against, so you know if you're getting the output you expect.

A few things to watch out for:

• Recursion and graphs are fairly advanced techniques; you might want to start with something easier. (Joe Celko has a good book on the topic, if you're interested.)
• There's often a big difference between a BIT and a C-style bool. At the very least, you may have to explicitly cast your output from INT to BIT.
• OUTER JOINs are useful when a portion of the data might be empty, but try not to abuse them.

Answer 9

I think it takes a while to adjust (it was long ago for me, so I don't remember too well). But perhaps the key point is that SQL is declarative - i.e. you specify what you want done, not precisely how it should be done procedurally. So for a simple example:

"Get me the names and salaries of employees in departments located in London"

The relevant SQL is almost natural:

select name, salary
from employees
join departments on departments.deptno = employees.deptno
where departments.location = 'London';

We have "told" SQL how to join departments to employees, but only declaratively (NATURAL JOIN removes the need to do that, but is dangerous so not used in practice). We haven't defined procedurally how it should be done (e.g. "for each department, find all employees...") SQL is free to choose the optimal method to perform the query.

Answer 10

Joe Celko's Thinking in Sets (book)

Perfectly intelligent programmers often struggle when forced to work with SQL. Why? Joe Celko believes the problem lies with their procedural programming mindset, which keeps them from taking full advantage of the power of declarative languages. The result is overly complex and inefficient code, not to mention lost productivity.

This book will change the way you think about the problems you solve with SQL programs.. Focusing on three key table-based techniques, Celko reveals their power through detailed examples and clear explanations. As you master these techniques, you’ll find you are able to conceptualize problems as rooted in sets and solvable through declarative programming. Before long, you’ll be coding more quickly, writing more efficient code, and applying the full power of SQL.