Hacker News Reader

Comment by Swizec | Hacker News Reader

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

View on HN

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 WHERE size = 45 BUT JUST [THEIR] color

All fairly tongue in cheek of course. SQL isn't going to change, so there's really no point in worrying too much about it :-)

> Usually when we select from a relational table, we select properties. In SQL, the drawer is not a thing

Ah but that’s by convention! If we want to be super pedantic, they’re all just relations. The drawer table describes a relation between values. And SELECT just defines a new relation! You’re creating an ad-hoc “table”.

That’s why “SELECT socks FROM (SELECT socks, pants FROM drawer)” works just fine.

Ok yes my brain has definitely been broken by years of SQL. I’m not the right person to understand newbies anymore.

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.

Trouble is that it falls apart when you have more than one operation. "select socks and pants from drawer, and delete money from drawer" is the weird sentence structure. Whereas if 'from' comes first: "from drawer, select socks and pants, and delete money". Which, granted, is still weird, but less so, and fixable with better word choice. Perhaps: "open drawer, select socks and pants, and remove money.

Codd's original vision would have seen that expressed as something like:

    RANGE DRAWER D
    GET W (D#,SOCKS,PANTS)
    DELETE D:(D.MONEY=1)

The only thing really missing in that from an English sentence structure perspective is generally we want just one more article in it and maybe a comma: from the drawer, select socks and pants.

But similarly I've written a lot of C# LINQ statements at this point and its from-first pattern seems to flow naturally enough that I don't see anything wrong with it (and a lot right, C# definitely has a great autocomplete experience under LINQ).

Both seem equivalent to me. And given how SQL is executed I like the latter approach tbh.

But generally SQL is often very nicely translatable to English at least for me and so long as the SQL isn’t too overly complex.

> If you started saying “from drawer select socks and pants”, wouldn’t that feel like a weird sentence structure to most people?

To a SQL Jedi order matters not.

Reply on news.ycombinator.com