Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Shouldn't FROM come before SELECT in SQL? (2011)

stackoverflow.com

Post reply on HN
Print view View original

71–80 of 137 posts

Re: Shouldn't FROM come before SELECT in SQL? (2011)

#71

Post by Delphiza »

pjmlp wrote: post #19

Azure query language's Kusto, lously based on a mix of F# and SQL, does it, as does LINQ. I end up always starting with the generic form select * from db, and then go from there, as code completion then works if there is at least one db.

You're probably getting downvoted just because of Azure, but you are right. ADX/Kusto and KQL is an extremely powerful query language that answers exactly what OP questions about SQL.

Here's an example of a KQL query that I have in my browser...

  Things
  | where DeviceTags has "Installed"
  | order by LastHeardFromTimeStamp desc 
  | take 10
KQL also takes ideas from R's Tidyverse and magrittr package. It takes datasets and pipes them into a new function. Like this...

  car_data %
    subset(hp > 100)
From the Microsoft auto downvoters out there, all Azure dashboards and infrastructure analytics run on KQL (think Graphana, but on Azure). There are billions of KQL queries executing continuously, so it is absolutely a good example of a non-SQL query language that is active and mature.

Re: Shouldn't FROM come before SELECT in SQL? (2011)

#74

Post by KittenPassingBy »

lucumo wrote: post #50
Swizec wrote: post #45

Earlier quoted context omitted.

> he idea that the from clause is evaluated/executed/defined first is a pain point Maybe my brain is broken by years of SQL and from learning English as a second language. But isn’t this supposed to follow a fairly mundane English sentence structure? “Select socks and pants from drawer”. If you started saying “from drawer select socks and pants”, wouldn’t that feel like a weird sentence structure to most people?

Slightly weird, though grammatically acceptably, as far as I know. But the debate is actually coming from something else your example shows nicely. When you select something from a drawer, you select entities. Usually when we select from a relational table, we select properties. In SQL, the drawer is not a thing. A different way to keep the English-style would be to add a BUT-JUST-[THEIR] clause. SELECT FROM socks WH…

Things are records / rows. Collections of the same things are entities (rubber band/ folders). Groups of entities are tables (Drawers).

A star schema usually contains one table to one entity mapping.

Dynamic tables for custom field on the other hand are usually multi entity tables. In this case, it would be a large junk drawer with various unrelated folders stuffed inside with everything from report cards, to keys to toys, to take out menus. It would have the toys entities, take out menu entities, keys entities, etc inside a mixed table.

Re: Shouldn't FROM come before SELECT in SQL? (2011)

#75

Post by dylan604 »

RodgerTheGreat wrote: post #61

The Lil[0] scripting language, like most APL-derived languages, has uniform operator precedence; expressions evaluate right-to-left unless you introduce parens: 3 * 2 + 1 9 (3 * 2) + 1 7 Lil includes an integrated query syntax which loosely resembles SQL. Queries begin with a command (select, update, extract), contain intermediate clauses in any order (where, orderby, by), and conclude with "from": select key value o…

WTF is right-to-left evaluation's purpose? It breaks the most fundamental rules of arithmetic. As a human that has been taught those rules, I'd never come up with 9 as an answer. Why did humans create a programming language that would?

Re: Shouldn't FROM come before SELECT in SQL? (2011)

#76

Post by dylan604 »

throwaway13337 wrote: post #44

Python's syntax also rattles me for the same reason. [item for item in items if item.include] It's almost exactly like a sql statement. The order is confusing. More broadly, foreach loops are also written in the wrong order. More reasonable: foreach(items as item) In the West, we read left to right. Presenting undefined terms before defined ones burdens the mind.

I !loved the coding style of (9 == $a) to protect against the common error of ($a = 9)

Re: Shouldn't FROM come before SELECT in SQL? (2011)

#77

Post by asavinov »

The sequence

    FROM Table AS t WHERE t.Condition SELECT t.col1, t.col2, ...
might be more natural than the traditional

    SELECT t.col1, t.col2, ... FROM Table AS t WHERE t.Condition
If we compare it with how loop are described in programming languages:

    Loop -> SELECT-FROM-WHERE
    Table -> Collection
    AS t -> Loop instance variable
    WHERE -> condition on instances
In Java and many other PLs, we write loops as follows:

    foreach x in Collection
        if x.field == value:
            continue
        // Do something with x, for example, return in a result set
So we first define the collection (table) we want to process elements from. Then we think about the condition they have satisfy by using the instance variable. And finally in the loop body we do whatever we want, for example, return elements which satisfy the condition.

In Python, loops also specify the collection first:

    for x in Collection:
Python list comprehension however uses the traditional order:

    [(x.col1, x.col2) for x in Collection if x.field2 == value]
Here we first specify what we want to return, then collection with condition.

Re: Shouldn't FROM come before SELECT in SQL? (2011)

#78

Post by RodgerTheGreat »

dylan604 wrote: post #75
RodgerTheGreat wrote: post #61

The Lil[0] scripting language, like most APL-derived languages, has uniform operator precedence; expressions evaluate right-to-left unless you introduce parens: 3 * 2 + 1 9 (3 * 2) + 1 7 Lil includes an integrated query syntax which loosely resembles SQL. Queries begin with a command (select, update, extract), contain intermediate clauses in any order (where, orderby, by), and conclude with "from": select key value o…

WTF is right-to-left evaluation's purpose? It breaks the most fundamental rules of arithmetic. As a human that has been taught those rules, I'd never come up with 9 as an answer. Why did humans create a programming language that would?

Traditional mathematical notation has precedence rules optimized for expressing polynomials. Programming languages- especially in the APL family- tend to have a richer collection of primitive verbs, and an "ideal" tower of binding precedence for all of them would be extremely complex, difficult to remember, and often unhelpful.

Unform evaluation order is much easier to remember and extend (no special cases!) and becomes even more natural than PEMDAS with a little practice. The APL family is hardly unique in this approach to precedence: Smalltalk has uniform infix precedence, Forths are uniformly postfix (unless you get goofy with parsing words), Lisps are uniformly prefix.

Re: Shouldn't FROM come before SELECT in SQL? (2011)

#79

Post by willvarfar »

Apreche wrote: post #37

FROM is not a command, it's a parameter, and an optional one at that. This is valid SQL. SELECT 1; The SQL commands are SELECT, UPDATE, INSERT, etc. Therefore, those commands should be the first thing in an instruction. If you have a file full of SQL, you probably want all the lines to start with those commands. Gonna be pretty weird to read if you have both SELECT and UPDATE lines that start with FROM. Probably diff…

An SQL parser has no problem supporting both forms as there is no parsing ambiguity.

(SQL is full of corner cases e.g. EXTRACT(WEEKDAY FROM field) etc. Putting the FROM first, or supporting multiple chained WHERE clauses or allowing WHERE before JOIN and applying to the preceding projection etc is all possible in an SQL parser that chooses to allow some relaxations. Personally, I am really irritated that I can't have HAVING without GROUP or QUALIFY without window functions etc, as I often construct queries programmatically.)

Re: Shouldn't FROM come before SELECT in SQL? (2011)

#80

Post by mwigdahl »

Apreche wrote: post #37

FROM is not a command, it's a parameter, and an optional one at that. This is valid SQL. SELECT 1; The SQL commands are SELECT, UPDATE, INSERT, etc. Therefore, those commands should be the first thing in an instruction. If you have a file full of SQL, you probably want all the lines to start with those commands. Gonna be pretty weird to read if you have both SELECT and UPDATE lines that start with FROM. Probably diff…

You could pretty easily achieve this with:

SELECT [FROM COLUMNS]

"SELECT 1" would still be valid, and you'd still have the commands first, but you could also get the benefits of IDE autocomplete for columns by specifying the table before the columns.

A little wordier though.

Post reply on HN