Why your query language should be explicit

Of course, if you're never joining, maybe that's not such a big problem, but you'd have the same issue of a specific range in your table grows disproportionally to another range in the same table and the index you are using is incorrect.

With SQL you can often be very explicit using Common Table Expressions as well in DBs that support it. Otherwise, using subqueries, GROUP BY with HAVING, and several other features often, but not always, prevent radically rewritten queries.

Finally, with SQL on many DBMSs you can still get the explicit wiggle room you need using optimizer hints. No, it's not very portable, but neither is ReQL.

Edit: A few examples of being explicit in SQL (a lá Oracle):

    SELECT /*+ INDEX(name) */ *
      FROM users
      WHERE users.name = 'jorge';


    WITH users_by_name AS (
      SELECT /*+ INDEX(name) */ *
        FROM users
        WHERE users.name = 'jorge'
    )
    SELECT * 
      FROM users_by_name
      JOIN profile using (user_id)
      ORDER BY age;

>Having your query language be explicit means that you've hit at exactly the right level of abstraction: not too much, but not too little.

I don't like the label of "explicit" as if it's an objective indicator on the continuum between low and high abstraction. It comes across as a value judgement that we'd all agree on and I don't think there's obvious consensus on architecting a data access language.

>If we wanted to dig deeper into this query, we might want to know if the "WHERE" is getting executed before the "ORDER BY". Can we tell from the query if this is the case? No, we can't. You'd have to look it up.

Not knowing the internals of execution is actually a deliberate design feature of SQL. The SQL is meant to be a declarative statement that expresses an algebraic set of rows. (But sometimes, the mathematical purity of this abstraction "leaks" and DBAs/Devs have to add HINTS or do SQL EXPLAIN PLAN to dig into what's happening under the hood -- but that's a separate issue.)

I suppose if one really wanted to affect the order of operations at the SQL syntax level, one could write a VIEW or a subquery with the ORDER BY and then write the outer query with the WHERE clause. I haven't tested this to see if any of the major SQL engines would rewrite this type of convoluted SQL of ORDER BY -then- WHERE clause.

Yes, with UNIX command line, you have different execution characteristics of "ls | grep | sort" vs "ls | sort | grep" but one can't translate that explicit-sequence-of-execution mental model to SQL.

* Does this increase cognitive load? Yes, it does. But this is outweighed by the ability to understand how your query is being executed.

I'm not convinced of this conclusion.

Also, I'm not sure RethinkDB works like this as a deliberately engineered advantage. The RethinkDB devs can clarify but it's possible for their engine to work like this because it's more straightforward to implement the parser and not because there is overwhelming inherent superiority to this approach. With traditional SQL (e.g. Oracle, MSSQL, etc), the query rewriting engines are very mature and can be more aggressive and thereby fulfilling the goal of a declarative mathematical purity. However, it takes lots of programmer man-hours to translate declarative SQL into optimal execution plans.

I feel like this is one of those "X is categorically better than Y" or "X is better than Y because Z" articles. The problem with these types of articles is that they tend to take an extreme position when the reality is much more nuanced. For the subset of relatively simple problems the author has encountered, perhaps the procedural query is easier to reason about, but there are vastly complex SQL queries that wouldn't benefit from an explicit query like this.