How to write SQL statement with dynamic where clause?

Will SQL ever be displaced by something better?

• I'm not talking about "NoSQL" which is a movement away from relational databases.  I am talking about SQL itself. SQL is a terrible interface for databases.  Every command has a six letter word in it.  The select clause should probably be optional, the where clause should probably be mandatory (with special syntax to explicitly state you want all rows). Constructing SQL in code is a pain.  You have to keep your commas and "and"s straight.  I'm pretty sure most of the reason ORM is popular is just because people don't want to write SQL. So will there ever be something better?  Maybe the databases can expose API's in higher level languages.  If not a better text based language is certainly possible. Something close to Scala code would be better.  Something JSON bases would be pretty succient and nice.  The kind of support for implicit joins that HQL has would be a huge help. Okay, I just wrote a whole little essay about what is bad about SQL without even trying.  So that raises a question.  Why is it so persistent?  Will something better ever come along?

• Answer:

  There are better things already, as you've already mentioned in your question: higher level APIs that abstract away all of the cumbersome stuff. ORM is a perfect example of something that uses SQL behind the scenes. You don't have to know SQL, you just have to know how the ORM framework works and know the language it's implemented in. Take Core Data, Apple's answer to ORM. It sits over the top of (usually) an SQLite database. A programmer using Core Data does not need to know anything about the underlying database at all, let alone what query language it uses or what features are available there. All they need to know is what functions the abstraction layer allows. On that note, HQL is itself a part of an abstraction layer for Java. The implicit joins are only a part of the features of Hibernate, of which HQL is a part. Using HQL, Hibernate that take care of the appearance of implicit joins (just like Core Data does with it's virtual object properties). The truth is HQL is an abstraction layer used to generate SQL queries under the hood. Given that there are existing abstraction layers above SQL, probably in your preferred programming language, why reinvent SQL itself?