SQL is a beautiful language, indeed. Writing vendor-specific SQL in stored procedures and views is great. But embedding SQL in other languages such as Java is still very hard in 2013.
Being a DSL with an AST, advanced SQL transformation can be implemented to standardise SQL where common SQL constructs aren't supported. These include:
jOOQ is the first and only API to fully embed the SQL language as an internal domain-specific language in Java, using an internal BNF for API definition: http://blog.jooq.org/2012/01/19/jooqs-fluent-api-in-bnf-nota...
Being a DSL with an AST, advanced SQL transformation can be implemented to standardise SQL where common SQL constructs aren't supported. These include:
- DISTINCT predicate: http://blog.jooq.org/2012/09/21/the-is-distinct-from-predica...
- Row value expression predicates: http://blog.jooq.org/2013/05/03/sql-query-transformation-fun...
- Derived column lists: http://blog.jooq.org/2013/01/07/simulating-the-sql-standard
Both vendor-specific SQL and embedded SQL have their merits.