SQL | John D. Cook

Here’s an excerpt from an email someone sent me in response to my post previous post Why SQL failed.

…

Your SQL blog article got my attention since I recently had to fix some SQL code that someone else wrote that has been in production for the last 4 years, running on a dataset containing about 3 million records. It performed some compound operations like the following.
SELECT *
FROM TESTTB2
WHERE
(COL1 IN (‘A', ‘B', ‘F')
AND
COL1 NOT IN (‘X','Y','Z')
OR
COL1 <> ‘R')
If the column COL1 contained null(s), then the NOT IN tests did not work as expected.

In the SQL code I was fixing, the SELECT missed 49,000 records that the programmer thought were being selected! The take-away message is that SQL requires attention to detail that often is not realized by the hobbyist programmer.

SQL (Structured Query Language) has been enormously successful as a tool for programmers. However, it completely failed at its intended purpose: a way for non-programmers to ask questions of a database.

I don’t think any other syntax would have been more successful. SQL syntax can be a little awkward, but that’s not the reason the general public doesn’t write SQL queries. The barrier to general use is that few people can form a moderately complex query precisely. Doing so requires thinking in terms of sets and logic. The difficulty is intrinsic to the problem; it’s not a problem of syntax.

I think about SQL every time I hear someone talking about how programmers are going to go away and that users will write their own software. And learning SQL is a pretty small task compared to learning everything else you need to know to develop software.

I also think about SQL when I hear someone talk about writing the ultimate report generator. Such a generator would be equivalent to SQL, and so it would take a programmer to use it. A good report generator is useful precisely because it is specialized to some context and therefore easier to use.

Update: I don’t want to imply that you need to be a professional programmer to understand SQL or that you need years of training. But you do need an aptitude for programming. Someone with an aptitude for programming might catch on to SQL quickly. But someone without an aptitude for programming is going to require a combination of incentive, patience, and training before they’ll ever use SQL. If Google’s home page required users to enter Boolean expressions, they would have gone bankrupt long ago.

Related post: Why there will always be programmers