About two weeks ago at work we discussed different strategies on testing databases. Although there are ways to do it, they are all more or less painful. And finally we all parted in agreeing: (SQL) databases suck, SQL sucks and testing databases and SQL sucks as well. We are only testing this crap because the only thing worse then database tests are untested databases. But complaining doesn't help. I have heard you are supposed to ask 5 times "why?" when faced with a problem. Maybe it was 7 times? Don't know lets start with asking it once:

Why does SQL suck?

Hard to parse for computers: I don't know much about parsing, but it seems to be hard for a computer to identify the bits and pieces of a SQL statement, when it is not really SQL but just almost SQL. This results in really crappy IDE support, because the editor doesn't know what you are trying to write, before you finished it.

Hard to parse for humans: One of the ideas behind the design of SQL was to make it look almost like a sentence. While this works for trivial statements, it completely falls a part when you have some serious statement at hand. For an extremely bad example check out the turing engine implemented in Oracle SQL. Side Note: aren't some fluent APIs trying to do just that.

Not consistent:Why does the having clause exist? Why nothing similar for group by? Who came up with the syntax of analytic functions. Why can I reference an expression defined the column list in the where clause, but not in the group by clause?

(Almost) no modularization: You need this real complex where condition in another select? Copy it. You need to join the same bunch of table, just with a tiny change in one condition? Copy it. You want to use the same group by clause in a different select? Copy it. There are few little things you can do to break your SQL code, but it is nothing compared to the power of a modern language like ... e.g. ... Fortran 66.

No decent development environment appart from the target environment When you write a java application (and you do it the way it is supposed to be done), most of it can get compiled, and executed in a simple JVM. No web server needed, nor application server. Compare that to SQL. The only thing that can properly compile and execute a SQL statement is a database. And not just any database, but a database with all the accessed tables created and filled with data. This does not help when writing SQL code.

Ok, we have some reasons for SQL sucking so badly. What's next. How about asking "Why?" again?

Why are all these things true about SQL?

That answer is easy: I have no idea. So let's step right ahead and ask again "Why"

Why does a curious developer with over 10 years of experience in database development know some reasons why SQL sucks, but can't answer the simple question, why these reasons apply?

I think it is because nobody really discusses SQL (at least not loud enough for me to hear). I know there is the No-SQL-Movement, but I happen to think that a relational database is a pretty decent solution to many things. There are also things like the Hibernate Criteria API and HQL. But those do not operate on database schemas, but on classes and objects. While this is fine when you wan't to work with classes and objects, it sucks when you just want to process large tables. When processiong 100K database records pulling each out of the database, instanciate an object, manipulating it and writing it back is not an option.

Yet a way to write queries in a more modular, easy to parse and easy to test way would be highly appreciated.