We Can Do Better Than SQL

A Brief History of the Relational Model and SQL

The relational model was introduced by Edgar F. Codd in a seminal 1970 paper “A Relational Model of Data for Large Shared Data Banks” [4]. There, Codd postulated that all data in a database can be represented in terms of sets of tuples, called relations. Codd also invented a form of first-order predicate logic to describe the database queries: tuple relational calculus.

Codd’s ideas were revolutionary. For the first time, a database, and a universal way to query it, was described in a succinct, consistent mathematical model. This, naturally, created lots of interest in further research and, importantly, into practical implementation of the relational model.

In 1974 Donald Chamberlin and Raymond Boyce published a paper [2], which introduced “a set of simple operations on tabular structures, […] of equivalent power to the first order predicate calculus”. Chamberlin and Boyce felt that the formal relational query languages proposed at the time were too hard to understand for “users without formal training in mathematics or computer programming” and thought that the “predominant use of the language would be for ad-hoc queries” [3]. Initially, the authors did not consider SQL to be a “serious” language. Nonetheless, the great interest in the commercial application of the relational model had pushed IBM to quickly adopt and productize SQL, which was also picked up by their quickly-rising competitor–Oracle.

IBM had an overwhelmingly large influence over the tech market at the time, so SQL became a de facto standard for relational databases, and then a proper standard with the publication of the first ANSI standard in 1989 that essentially circumscribed the most prominent existing implementations of SQL. Subsequent versions of the standard continued to be primarily influenced by the commercial vendors.

Today, SQL is by far the most widely used database language. But that does not necessarily mean that it represents the best of what we can do. In fact, SQL’s beginnings as a “simple, ad-hoc” language coupled with “design by implementation” from competing vendors had left the language with a baggage of severe issues.

Critique of SQL

SQL, especially its earlier versions, was heavily criticized by the experts, including Codd himself [6], as well as C. J. Date, who published a multitude of papers and books on the subject ([7], [9], [10]). While many of the early shortcomings were fixed in the later versions of the standard, some of the serious issues had been only further ingrained.

Some of the complaints here apply to SQL as a whole, while others are specific to a certain implementation. We are primarily using PostgreSQL for the examples.

SQL’s shortcomings can be grouped into these categories:

• lack of proper orthogonality — SQL is hard to compose;

• lack of compactness — SQL is a large language;

• lack of consistency — SQL is inconsistent in syntax and semantics;

• poor system cohesion — SQL does not integrate well enough with application languages and protocols.

SELECT name
FROM emp
WHERE role = 'dept head'

SELECT
    dept.name,
    ...
    (SELECT name FROM emp
     WHERE role = 'dept head' AND deptno = dept.no)
FROM
    dept
    ...

SELECT
    dept.name,
    ...
    head.name
FROM
    dept
    INNER JOIN emp AS head
        ON (head.deptno = dept.no
            AND head.role = 'dept head')
    ...

SELECT substring('PostgreSQL' from 8 for 3);
SELECT substring('PostgreSQL', 8, 3); -- PostgreSQL-only syntax

SELECT trim(both from 'yxSQLxx', 'xyz');
SELECT extract(day from timestamp '2001-02-16 20:38:40');

SELECT array_agg(foo ORDER BY bar)

SELECT rank(foo) WITHIN GROUP (ORDER BY bar)

postgres=# select NULL = NULL;
 ?column?
----------

(1 row)

postgres=# CREATE TABLE x (a int, b text);
CREATE TABLE
postgres=# INSERT INTO x(a, b)
           VALUES (1, 'one'), (2, 'two'), (NULL, 'three')
           RETURNING a, b;
 a |   b
---+-------
 1 | one
 2 | two
   | three
(3 rows)

postgres=# SELECT * FROM x WHERE a NOT IN (1, null);
 a | b
---+---
(0 rows)

elvis=# SELECT DISTINCT *
        FROM (VALUES (1), (NULL), (NULL)) AS q;
 column1
---------

       1
(2 rows)

elvis=# VALUES (1), (NULL) UNION VALUES (2), (NULL);
 column1
---------

       1
       2
(3 rows)

postgres=# SELECT count(*) FROM x WHERE a = 1 OR a != 1;
 count
-------
     2
(1 row)

postgres=# SELECT
               b,
               CASE WHEN a = 1
               THEN 'one'
               ELSE 'not one'
               END
           FROM x;
   b   |  case
-------+---------
 one   | one
 two   | not one
 three | not one
(3 rows)

postgres=# SELECT
               b,
               CASE WHEN a != 1
               THEN 'not one'
               ELSE 'one'
               END
           FROM x;
   b   |  case
-------+---------
 one   | one
 two   | not one
 three | one
(3 rows)

postgres=# SELECT count(a) FROM x;
 count
-------
     2
(1 row)

postgres=# SELECT cardinality(array_agg(a)) FROM x;
 cardinality
-------------
           3
(1 row)

postgres=# SELECT count(*) FROM x;
 count
-------
     3
(1 row)

postgres=# SELECT (NULL, 1) = (NULL, 1);
 ?column?
----------

(1 row)

postgres=# SELECT (NULL, 1) IS NULL;
 ?column?
----------
 f
(1 row)

postgres=# SELECT (NULL, 1) IS NOT NULL;
 ?column?
----------
 f
(1 row)

postgres=# SELECT (ARRAY[1, 2])[3];
 array
-------

(1 row)

postgres=# SELECT to_char(timestamp '2001-02-16 20:38:40', '');
 to_char
---------

(1 row)

mysql> SELECT 1 / 0;
+-------+
| 1 / 0 |
+-------+
|  NULL |
+-------+
1 row in set, 1 warning (0.00 sec)

Why Does All This Matter?

OK, so we have highlighted the shortcomings of SQL. Why do they matter? It’s all about ergonomics. Orthogonality, compactness, and consistency are all essential traits of a programming language that is easy to learn and use effectively on every level of expertise, team size, and project complexity.

We have become accustomed to a constant improvement and reimagination of programming languages. Swift, Rust, Kotlin, Go, just to name a few, are great examples in the advancement of engineer ergonomics and productivity. But SQL, often hidden behind layers of ORMs and frameworks, is still very much the dominant data language.

The NoSQL movement was born, in part, out of the frustration with the perceived stagnation and inadequacy of SQL databases. Unfortunately, in the pursuit of ditching SQL, the NoSQL approaches also abandoned the relational model and other good parts of RDBMSes.

EdgeQL: Query Language Evolution

The relational model is still the most generally applicable and effective method of representing data. The concept of SQL as a declarative, storage-neutral query language is powerful and versatile. We don’t need to abandon either. What we do need is a “better SQL”: a query language that affords more power to its users, but that is also simpler and more consistent.

This is exactly what we are working hard to achieve with EdgeQL. We spent years of research and development, focusing on usability and performance without compromising correctness. In our earlier blog post we described some of the great features of the language, but it’s worth getting into some detail here to highlight how we are solving the issues brought up in this post.

SELECT Movie {
    description,
    number_of_reviews := count(.reviews)
};

SELECT math::mean(
    Movie {
        description,
        number_of_reviews := count(.reviews)
    }.number_of_reviews
);

SELECT (
    avg := math::mean(
        Movie {
            number_of_reviews := count(.reviews)
        }.number_of_reviews
    ),
    max := max(
        Movie {
            number_of_reviews := count(.reviews)
        }.number_of_reviews
    )
);

WITH
    MovieReviewCount := Movie {
        number_of_reviews := count(.reviews)
    }
SELECT (
    avg := math::mean(MovieReviewCount.number_of_reviews),
    max := max(MovieReviewCount.number_of_reviews),
);

edgedb> 

SELECT True OR <bool>{};

{}

edgedb> 

SELECT True AND <bool>{};

{}

edgedb> 

SELECT count({});

{0}

edgedb> 

SELECT array_agg(<str>{});

{[]}

edgedb> 

SELECT {1, {2, 3}, {}, {}};

{1, 2, 3}

edgedb> 

SELECT {1} UNION {2, 3} UNION {} UNION {};

{1, 2, 3}

edgedb> 
....... 

WITH empty_set_expr := <int64>{}
SELECT empty_set_expr ?? {1, 2};

{1, 2}

edgedb> 
....... 

WITH empty_set_expr := <int64>{}
SELECT {1, 2, 3} IF EXISTS empty_set_expr ELSE 42;

{42}

SELECT Movie {
    description,

    directors: {
        full_name,
        image,
    }
    ORDER BY .last_name,

    cast: {
        full_name,
        image,
    }
    ORDER BY .last_name,

    reviews := (
        SELECT Movie.<movie[IS Review] {
            body,
            rating,
            author: {
                name,
                image,
            }
        }
        ORDER BY .creation_time DESC
    ),
};

Final Words

SQL started with a goal to empower non-programmers to work with the relational data effectively. Despite its shortcomings, it has arguably been wildly successful, with most databases implementing or emulating it. However, like any solution, SQL is facing increasing inadequacy in the support of the new requirements, modes of use and user productivity. It is time we do something about it.

EdgeDB on GitHub

[1] Atzeni P. et al., The relational model is dead, SQL is dead, and I don’t feel so good myself. ACM SIGMOD Record, 42(2):64-68, 2013.

[2] Chamberlin D. D, Boyce R. F., “SEQUEL: A Structured English Query Language”, ACM SIGFIDET 1974, pp 249-264.

[3] Chamberlin D. D, “Early History of SQL”, IEEE Annals of the History of Computing, 34(4):78-82, 2012

[4] Codd E. F., “A relational model of data for large shared data banks”, Communications of the ACM CACM, 13(6):377-387, 1970

[5] Codd E. F., “More commentary on missing information (applicable and inapplicable information)”, ACM SIGMOD Record 16(1):42-47, 1987.

[6] Codd E. F., “The relational model for database management: version 2” Addison-Wesley, Mass. 1990.

[7] Date C. J., “A critique of the SQL database language”, ACM SIGMOD Record 14(3):8-54, 1984.

[8] Date C. J., “Null Values in Database Management. In Relational Databases: Selected Writings”, Addison-Wesley, Mass. 1986.

[9] Date C. J., “Where SQL falls short”, Datamation 33(9):83-86, 1987.

[10] Date C. J., “SQL and Relational Theory”, O’Reilly, 2009