Why is SQL used so narrowly?

Despite being one of the most widely used languages in IT, a great number of tools we use in the trade today exist solely to work around its limitations; some inherent to the language, and some existing purely in our collective mind.

As I keep encountering different query languages over the years, the question “why not SQL?” keeps surfacing in my mind. LINQ. GraphQL. JQ. Malloy. Why do we have so many declarative languages for working with data, all resembling SQL either in syntax, application, or capability, all supporting a subset of the same basic operations? Even some capabilities of modern SQL feel like they’re reinventing the core language. Structured data types reinvent projection, selection, ordering, etc. Window functions reinvent aggregations. Semantic models reinvent relations, except they define relations over rows instead of atomic values.

In this article, I will dive into the characteristics of the SQL language and its perception in the industry that have, and continue to, drive fragmentation in the query language space.

SQL denormalizes data

The whole premise of relational databases is that data is stored in normal form. Conventionally, SQL queries return a table, which facilitates a simple UI, has abundant data interchange formats, and lineage tracing back 4000 years. Counterintuitively, queries performing any kind of cardinality expansion effectively denormalize data. However, many applications do actually call for normalized data and this is where SQL falls short.

Let me give you an example:

select customer.name, order.date, order.status
from customer
left join order on customer.id = order.customer_id;

If a customer has made three orders, their name will appear in the result three times. While it makes perfect sense if you specifically want the result to be a table, typical applications actually benefit from a denormalized representation.

Imagine rendering the result in a UI widget which requires a shape {customer_name: string, orders: {date: date, status: string}[]}. You are forced to either group the resulting rows by the customer name and filter out the nulls, or to perform N+1 queries.

Therefore, an application developer needs to choose between performing some query processing outside the DBMS or using a different query language altogether. For example, GraphQL supports nesting, which keeps data normalized.

It sounds like a detail but it’s not. We’ve been retrofitting nesting for decades in the form of structured and semi-structured data types and functions, which leak what are essentially relational operations into the expression syntax, and their results require a PhD in lateral flatten to join or aggregate.

Semantic models

A DBMS user typically thinks about how rows across different tables relate to one another. For example, each customer might make many orders, there may be many items in each order, each with its own quantity. This data is spread across many tables, e.g. customer, order, order_item, and putting it together using SQL typically requires knowing the semantic model, i.e. how exactly it was split up: for example, order.customer_id is a foreign key to customer.id, or worse, relations between orders and items are stored in the order_item table.

This information is often partially available in the schema as keys and constraints. Ironically though, we only access this information when optimizing the query, and not from within the query itself. As in, I can’t express “the items in this order” directly in SQL—I need to perform a join using columns which might have no meaning beyond the semantic model itself.

Supplying this knowledge in every query is impractical. Provisioning it in advance using a view has its own pitfalls. For example, imagine a view that joins all customers, orders, and items. Then select customer_name from customers_and_orders suffers from cardinality explosion due to the denormalization issue I discussed before—each name will appear in the result multiple times. All in all, this is one of the key reasons why semantic models are built outside the DBMS.

Relations over relations

As an interesting aside, I used the term “relation” above in its mathematical sense, i.e. as a subset of the carthesian product of multiple sets. In this instance, it was a set of semantically correct combinations of rows from different tables. In relational algebra, which is at the heart of relational databases, the term “relation” is used to denote a single table, and the sets being multiplied are the domains of each columns’ data types.

Thus, curiously, a semantic model can be viewed as a relation over relations, or nested relations. The term “relational database” aged quite badly given its inability to express some of the relations its users care about deeply, even as the algebra paved the way for a lot of excellent query planner work.

SQL is not a query language

If it were introduced today, it would have most likely been classified as a shell script. The actual query language is a subset of SQL called DQL—i.e., the statements starting with select, or with, or show. DDL, DML, DCL are essentially APIs for mutating state which are made to look similar to DQL, and only sometimes embedding DQL like in the create view statement.

Instead, imagine if SQL were introduced as a stack consisting of DQL, the query language, and SQL, the shell, layered on top. Building a full DBMS? Support both. Implementing a util for querying local CSVs? Make it DQL-compliant—with no need to mention SQL anywhere. The perception of how they fit into the software landscape would be wildly different. In my view, DQL would have been used in more applications simply because it wouldn’t be mentally associated with storage, metadata, transactions, access control, and other problems that non-DBMS software often doesn’t need to solve. Instead, our understanding of where SQL belongs is anchored to DBMS.

“I’m tired, boss”

It’s an old language and it shows. Idiosyncrasies below are not fundamental issues, but have an impact on how the language is perceived and thus, its adoption in new areas.

Two heavily overlapping syntaxes

How do you apply a predicate? Using where, or maybe by applying a filter function on an array? The commonly available (semi-)structured functions are powerful and often used to work around SQL’s tabular nature, but are insufficient to perform joins and aggregations. where is duplicated by filter, distinct by array_distinct, semi-joins by array_contains, rank by array_position, order by by array_sort… the list goes on.

Too many people scream in SQL

And that’s just rude. There’s a reason we use uppercase letters sparingly—we now have color displays and syntax highlighting. The advantage of uppercasing keywords is long gone but the habit remained, keeping alive the neurons somehow linking SQL to BASIC, and subliminaly influencing our decision-making. Laugh all you want, you know it’s true. But it gets worse.

SQL is case-insensitieve

It’s bad enough when keywords are case-insensitive, but so are SQL identifiers. As a result, ingesting a case-sensitive source such as Kafka to a case-insensitive DBMS might accidentally merge two different topics t and T into a single table. Even worse, with a sufficient number of users, some of them will still scream that table’s name.

Aggregate functions do not expose invariants

If a column a only contains nulls, sum(a) returns null, not 0.

* is not an equivalent of “all columns”

If a table with columns a int, b int contains a single row filled with nulls, count(*) yields 1 while count(a, b) yields 0.

A path forward

There’s no doubt that the world would benefit from a modern successor to SQL, or more specifically, DQL. Achieving SQL’s levels of adoption would be nothing short of a miracle, but there are a few projects that are trying to move the needle. Good examples are Malloy and EdgeQL, the latter of which having a particularly beautiful type system.

Personally, I think the industry underappreciates universality as a property of a language. In order to judge how universal a given language is, we’d need to estimate how capable it is of serving the existing use cases for SQL, GraphQL, LINQ, and others. This is something I’m actively exploring and will continue writing about. Stay tuned!