5 ms·

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 -> SELE

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>`.

kerkeslager 1y ago
> 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.
- sgarland 1y ago
  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/parser https://github.com/postgres/postgres/tree/master/src/backend...
  - jagged-chisel 1y ago
    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.
  - 9rx 1y ago
    > 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.
  - kerkeslager 1y ago
    > 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.
mamcx 1y ago
> 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`
- sgarland 1y ago
  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://cs186berkeley.net/notes/note6/ https://web.wlu.ca/science/physcomp/ikotsireas/CP465/W1-Intro-Review/RelationalAlgebra.pdf https://web.wlu.ca/science/physcomp/ikotsireas/CP465/W1-Intr... https://home.adelphi.edu/~siegfried/cs443/443l9.pdf https://home.adelphi.edu/~siegfried/cs443/443l9.pdf
  - deleted 1y ago
    [deleted]
  - mamcx 1y ago
    You are correct in that is the notation used, but that is not how it goes internally!
balfirevic 1y ago
> 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.
- 9rx 1y ago
  > 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.
  - balfirevic 1y ago
    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".
9rx 1y ago
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 https://dl.acm.org/doi/pdf/10.1145/1734714.1734718
- sgarland 1y ago
  What section did you see this? I see GET (i.e. SELECT) first.
  - 9rx 1y ago
    Section 3.3. "SELECT" only comes first if you omit "FROM". In SQL, if it allowed, that would be something like: `SELECT table.column`.
    - sgarland 1y ago
      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)
      - 9rx 1y ago
        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.
bazoom42 1y ago
> 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.
marzell 1y ago
> 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.
- sgarland 1y ago
  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.
  - jodrellblank 1y ago
    You apparently can in SQLIte though: https://sqlfiddle.com/sqlite/online-compiler https://sqlfiddle.com/sqlite/online-compiler and query: SELECT LOWER(Name) as LName FROM Product where LName like '%frame%';
  - marzell 1y ago
    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.
stonemetal12 1y ago
>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.

kerkeslager 1y ago

> 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.

sgarland 1y ago
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/parser https://github.com/postgres/postgres/tree/master/src/backend...
- jagged-chisel 1y ago
  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.
- 9rx 1y ago
  > 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.
- kerkeslager 1y ago
  > 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.

mamcx 1y ago

> 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`

sgarland 1y ago
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://cs186berkeley.net/notes/note6/ https://web.wlu.ca/science/physcomp/ikotsireas/CP465/W1-Intro-Review/RelationalAlgebra.pdf https://web.wlu.ca/science/physcomp/ikotsireas/CP465/W1-Intr... https://home.adelphi.edu/~siegfried/cs443/443l9.pdf https://home.adelphi.edu/~siegfried/cs443/443l9.pdf
- deleted 1y ago
  [deleted]
- mamcx 1y ago
  You are correct in that is the notation used, but that is not how it goes internally!

balfirevic 1y ago

> 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.

9rx 1y ago
> 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.
- balfirevic 1y ago
  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".

9rx 1y ago

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 https://dl.acm.org/doi/pdf/10.1145/1734714.1734718

sgarland 1y ago
What section did you see this? I see GET (i.e. SELECT) first.
- 9rx 1y ago
  Section 3.3. "SELECT" only comes first if you omit "FROM". In SQL, if it allowed, that would be something like: `SELECT table.column`.
  - sgarland 1y ago
    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)
    - 9rx 1y ago
      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.

bazoom42 1y ago

> 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.

marzell 1y ago

> 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.

sgarland 1y ago
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.
- jodrellblank 1y ago
  You apparently can in SQLIte though: https://sqlfiddle.com/sqlite/online-compiler https://sqlfiddle.com/sqlite/online-compiler and query: SELECT LOWER(Name) as LName FROM Product where LName like '%frame%';
- marzell 1y ago
  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.

stonemetal12 1y ago

>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.