The Structured (English) Query Language

This is just a small observation about one counterintuitive feature of the SQL language. SQL (Structured Query Language) was initially named SEQUEL (Structured English Query Language). One word clearly explains why the “keyed-in” SQL statements order is different than the logical query processing order.

Say, we’d like to ask someone for a bottle of beer from a fridge. The first thing we think about is what do we want (a beer) and only then how to get it (from the fridge).

In English, we’d say:

Give me a bottle of beer from the fridge, please.

First, what do we want, second, how to get it.

But if we wrote a program, we want to make an algorithm: first — how, second — what:

Open the fridge, take a bottle of beer.

The so-called “keyed-in order” of a standard SQL query is:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

But the logical query processing order is different:

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

This is why we can’t, for example, refer in the WHERE clause to a column alias defined in the SELECT clause. This isn't allowed because the WHERE clause is evaluated before the SELECT clause.

Now take a classic SQL query:

SELECT Country, YEAR(HireDate) AS YearHired, COUNT(*) AS NumEmployees
FROM Employees
WHERE HireDate >= '19920101'
GROUP BY Country, YEAR(HireDate)
HAVING COUNT(*) > 1
ORDER BY Country, YearHired DESC

And compare to it’s LINQ-expression equivalent:

var result =  from e in Employees
              where e.HireDate >= new DateTime(1992, 1, 1)
              group e by e.Country into g
              where g.Count() > 1
              orderby g.Key descending
              select new { g.Key, Count = g.Count() };

The select at the last line looks more natural, isn't it?

Of course, orderby goes before the select, but this is exactly how it is processed in the LINQ code:

var result2 = Employees
    .Where(e => e.HireDate >= new DateTime(1992, 1, 1))
    .GroupBy(e => e.Country)
    .Where(g => g.Count() > 1)
    .OrderByDescending(g => g.Key)
    .Select(g => new { g.Key, Count = g.Count() });