Hacker News Reader

Comment by sgarland | Hacker News Reader

It's written that way because it stems from relational algebra, in which the projection is typically (always?) written first.

>The order should be FROM -> SELECT -> WHERE, since SELECT commonly gives names to columns, which WHERE will reference.

Per the SQL standard, you can't use column aliases in WHERE clauses, because the selection (again, relational algebra) occurs before the projection.

> You could even avoid crap like `SELECT * FROM table`, and just write `FROM table` and have the select clause implied.

Tbf, in MySQL 8 you can use `TABLE <table>`, which is an alias for `SELECT * FROM <table>`.

View on HN

> It's written that way because it stems from relational algebra, in which the projection is typically (always?) written first.

It's inspired by a mish-mash of both relational algebra and relational calculus, but the reason why SELECT comes first is because authors wanted it to read like English (it was originally called Structured English Query Language).

You can write the relational algebra operators in any order you want to get the result you want.

> You can write the relational algebra operators in any order you want

Ultimately, yes, you can express relational algebra in any notation that gets the point across, but the parent is right that

   π₁(R)

is what is commonly used.

   (R)π₁

not so much. Even Codd himself used the former notation style in his papers, even though he settled on putting the relation first in his query language.

My impression was the parent poster was talking about order of operations like projection and selection, where you might more commonly write:

Π(σ(R)) instead of σ(Π(R))

and not about whether relational algebra uses prefix or postfix notation:

Π(σ(R)) vs. R > σ > Π

SQL's WHERE statement (and others) works totally differently from SELECT in that regard, so it doesn't make much sense to say that "SELECT comes first because relational algebra".

Interestingly, the inventor of relational algebra for database management put the "FROM" first in his query language: https://dl.acm.org/doi/pdf/10.1145/1734714.1734718

What section did you see this? I see GET (i.e. SELECT) first.

Section 3.3. "SELECT" only comes first if you omit "FROM". In SQL, if it allowed, that would be something like: `SELECT table.column`.

Tuple Variables and their Range?

I don’t think I see what you see. From 3.3:

    RANGE PART P
    GET W (P.P#,P.PNAME,P.QOH):(P.QOH<25)

Which approximately translates to:

   SELECT p.pnum, p.pname, p.qoh FROM part p WHERE p.qoh < 25

But a more direct translation would look something like:

   FROM part p SELECT p.pnum, p.pname, p.qoh WHERE p.qoh < 25

Even closer would be:

   FROM part p; SELECT p.pnum, p.pname, p.qoh WHERE p.qoh < 25

Of course they are different languages so we can only take things so far, but the pertinent bit is that the range specifier is declared first in ALPHA, whereas it is declared later, after the projection is defined, in SQL.

> It's written that way because it stems from relational algebra,

A common misconception (that SQL is a realization of RA instead of barely based on it).

In RA, is in fact `Relation > Operator`

Unless I am grossly misunderstanding your notation, I have always seen RA written with the operator first. Some examples:

https://cs186berkeley.net/notes/note6/

https://web.wlu.ca/science/physcomp/ikotsireas/CP465/W1-Intr...

https://home.adelphi.edu/~siegfried/cs443/443l9.pdf

You are correct in that is the notation used, but that is not how it goes internally!

> It's written that way because it stems from relational algebra

More likely because this order is closer to typical English sentence structure. SQL was designed to look like English, not relational algebra.

> Per the SQL standard, you can't use column aliases in WHERE clauses, because the selection (again, relational algebra) occurs before the projection.

Except this works in most major vendor SQL implementations. And they all support relation aliases in SELECT... Seems the standards have long fell behind actual implementations.

To clarify, I mean you can’t do this:

    SELECT id AS foo
    FROM MyTable
    WHERE foo = 1;

Similarly, you can’t do this:

    SELECT id
    FROM MyTable
    WHERE id = MAX(id);

Because in both cases, when the WHERE predicate is being executed, the engine doesn’t yet know what you’re asking it to find - for the former, SELECT hasn’t yet been called, so the alias isn’t applied; for the latter, the aggregation happens after filtering, so it can’t know what that maximum value is.

You can of course alias columns for SELECT however you’d like, and can also use those aliases in ORDER BY. You can also trivially refactor the above examples to use subqueries or CTEs to accomplish the goal.

You apparently can in SQLIte though: https://sqlfiddle.com/sqlite/online-compiler and query:

    SELECT LOWER(Name) as LName FROM Product where LName like '%frame%';

You absolutely can in many engines, for instance in Snowflake... with the small exception that in all supporting engines you actually need to use HAVING instead of WHERE in your second example (because it compares an aggregation, otherwise WHERE is fine).

You can also use "correlated column aliases" (I can't recall the proper name) i.e.

    SELECT
        id AS foo,
        foo || '_1' as foo_n,
        right(foo_n, 1) as foo_ordinal
    FROM MyTable
    WHERE foo = 1;

Again, if this isn't all part of SQL standards, the reality is that a lot of engines have semi-standard (sometimes very proprietary too) ways of handling these now common patterns. For real-world use cases, the standards are unfortunately becoming increasingly irrelevant. I think it would be better in the long term to use standards, but if they can't keep up with actual usage then they will just get ignored.

>Per the SQL standard, you can't use column aliases in WHERE clauses, because the selection (again, relational algebra) occurs before the projection.

Don't blame the math for sloppy implementation. Nothing in the math suggest where aliases should be specified. Don't get me wrong it is a coinvent, logical place to put them, but as you say it rather limits their use, and could have been done better.

> It's written that way because it stems from relational algebra, in which the projection is typically (always?) written first.

Okay, so what?

We're not obligated to emulate the notational norms of our source material, and it is often bad to do so when context changes.

I was explaining why it is the way that it is. If you'd like your own version of a parser, here's Postgres' [0]. Personally, I really like SQL's syntax and find that it makes sense when reading it.

[0]: https://github.com/postgres/postgres/tree/master/src/backend...

There was not argument about how much sense it makes. There was an argument for improving readability by placing the table names first.

Lots of people “like” things because they are familiar with them. And that’s a fine enough reason. But if you step out of your zone of familiarity, can you find improvements? Are you willing to forgo any prejudice you may possess to evaluate other suggestions?

Just a little willingness to see another perspective is all anyone asks.

> If you'd like your own version of a parser, here's Postgres' [0].

Funnily enough, if you pull up a version of Postgres' parser prior to the 1995 release, you'll find that it puts the relation first.

> I was explaining why it is the way that it is.

No one was confused what you were doing.

I'm saying the historical reason for why it is the way it is, doesn't matter. I would hope we design our languages to be maximally clear and useful, not to be maximally full of historical cruft.

There's no accounting for taste, so you're welcome to like whatever you like, but I don't think you liking a backward syntax is particularly persuasive. It sounds more like you're just used to it than that you see any actual benefits to it.

Reply on news.ycombinator.com