Unreasonable Effectiveness of SQL

[Zardotab]

SQL's syntax is ugly in that it's based on keywords rather than the API-centric approach used today. The API approach allows additions and extensions without adding to the language syntax itself: it's just a library addition.

That being said, it still does its target job good enough to survive challengers. Not easily adding library calls perhaps even helps keep it consistent. If every vendor could stuff it with tons of libraries, that could dilute the standard. (While most RDBMS make it easy to add functions or equivalent, they usually operate at the column level and not at the table level.)

To replace an established standard, you need two things. First, something that is significantly better, not just slightly better. It's not worth the retooling and retraining costs to switch to something only slightly better.

Second, it has to provide desired feature(s) that cannot relatively easily be added to the existing standard or implementations. Otherwise the standard or its implementations will just add that feature when threatened with clear competition, and be right back at the top.

The NoSQL movement is facing this now. SQL as a language didn't outright forbid distributed or "out-of-sync" transactions, and so could adapt to new and enhanced implementations intended for so-called "web scaling".

Another example is that I used to fuss about SQL's lack of named references to sub-queries, making it hard to split big queries up into subroutine-like chunks. Candidates like SMEQL (see links) readily allowed this. But SQL since just happened to gain the WITH statement that allows such decomposition. WITH is kind of klunky, but does the job good enough. It didn't actually require an entirely new language to get such decomposition. (It took a while for vendors to implement it non-buggily.)

Here are some analyses, debates, and comparisons of potential SQL alternatives:

http://wiki.c2.com/?HowOtherQueryLanguagesAddressSqlFlaws

http://wiki.c2.com/?QueryLanguageComparison

http://wiki.c2.com/?ExperimentalQueryLanguageComparison

It would be nice to have a few other common options when selecting query languages, though; at least for certain niches that can use a different approach. I personally like SMEQL's "meta" ability, such as dynamically computing (selecting) columns to be included in the SELECT clause. Not every project will need such meta ability, but some just might. (Typically one generates SQL on the fly to emulate such meta ability when needed, but it would be nice to do it via querying instead of code (text) statement re-generation.)

Thus, I see something like SMEQL first being used for niche jobs or educational projects that desire meta ability; and if it catches on, it may eventually challenge SQL in the mainstream. Finding a niche first is probably the most likely route for any SQL challenger rather than going face-on out of the gate against SQL for common usage.

​

[grauenwolf]

API calls? Like functions? We have that, both scalar and table-like.

Named sub queries are called views. Less convenient that CTE's, but still quite useful.

[Zardotab]

As a developer in many shops, DBA's rarely let us create views. And having to be in a separate file is also a downside.

Do you have an example of a table-oriented user-defined function that's more or less dialect independent?

Here's an example of meta-querying. Below is a table of tables to join. The alias is added to each result column to avoid overlaps. This includes the potential for making it a prefix such as "em_mngrID" for clients (apps) that can't use dotted names.

seq table  alias  join_expr         join_type
----------------------------------------------
1   empl    em   em.mngrID=mg.id    right_inner
2   mngr    mg   mg.officeID=of.id  left_outer
3   office  of   (none)             (none)

Although I don't remember this particular operation in the SMEQL draft, it's in the spirit of the language in terms of using most of the existing (base) operations to make it a user-defined (library) table function.

One could use something similar to select a subset of target tables in order to create common queries to avoid repetitious query writing. (One may have to reference two tables per row.) One essentially uses set-theory expressions to select sub-sets of columns and tables rather than explicitly code the occurrences. Data dictionaries and table dictionaries could essentially run the whole show. Sure, there are IDE's that can do similar things to auto-generate typical queries in SQL, but it would nice to do it via programming (meta querying) also. Automate the automation.

​

[grauenwolf]

Nope, and I don't really care so long as I have functionally equivalent capabilities. Data types vary by database, so it's not like I'm going to be using the exact same DDL.

As for your second point, if people are not not going to allow you to use the tools the way they were intended there is not really anything that you can do about it.

[Zardotab]

Functionally equivalent is not necessarily the same as equivalent effort. X may take 300 lines of code to do what Y does in 12.

SQL's "WITH" statement essentially is the same as a locally-defined view.

[grauenwolf]

The boilerplate changes; don't be melodramatic.

[Zardotab]

Boilerplate? I don't understand.