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

Post by hot_gril » Thu, Jan 25, 2024, 8:39 PM UTC

hot_gril wrote: post #114

silvestrov wrote: post #92

Earlier quoted context omitted.

It would be really nice if PostgreSQL and sqlite implemented this natively as it really feels how SQL should be (re)designed today. It also makes writing complex queries easier as it would be possible to split out actions in multiple steps where you can see what columns and types you have created (and found) so far. Complex SQL is like complex C++: works if you get it right, but no help whatsoever for figuring out wh…

On one hand SQL has some oddities, on the other I'd rather there not be yet more different-but-equivalent ways to do things in SQL. I think the current state is good enough.

Post by randomdata » Thu, Jan 25, 2024, 9:29 PM UTC

Swizec wrote: post #45

gigatexal wrote: post #39

Earlier quoted context omitted.

that may very well be the case but perhaps there could be an evolution of the language because for newcomers the idea that the from clause is evaluated/executed/defined first is a pain point. Newbies I help sometimes wonder why things they reference in the SELECT part aren't visible/available in the FROM clause and that's because one is selecting the result of FROM ... JOIN ... WHERE etc anyway

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

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

Post by hn92726819 » Thu, Jan 25, 2024, 10:54 PM UTC

rhplus wrote: post #81

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…

FROM ℝ SELECT 1 ? :-)

    ORDER DESC
    COUNT(*)

Post by jasomill » Fri, Jan 26, 2024, 5:19 AM UTC

hn92726819 wrote: post #126

rhplus wrote: post #81

Earlier quoted context omitted.

FROM ℝ SELECT 1 ? :-)

I wonder if there is a concept of infinite length SQL tables. Not sure what would happen with: ORDER DESC COUNT(*) I can't find anything after a quick search except recursive views that must terminate (therefore aren't infinite)

  > SELECT COUNT(*) FROM ℤ;
  ℵ₀
  > SELECT COUNT(*) FROM ℝ;
  POWER(2,ℵ₀)
  > EXEC sp_configure 'continuum hypothesis','1';  
  > RECONFIGURE;
  > SELECT COUNT(*) FROM ℝ;
  ℵ₁

Post by deepsun » Fri, Jan 26, 2024, 7:43 AM UTC

The_Colonel wrote: post #106

deepsun wrote: post #87

Earlier quoted context omitted.

Nitpick -- SQL was not designed to be used programmatically. Same as shell commands, it's for humans to write manually. Proper API could have way more concise, and much more efficient format to parse. And would avoid a bunch of security issues along the way, same as for shell.

This specific issue is a problem for humans, not for APIs. I have a very smart SQL IDE with great intellisense, but when I type "SELECT", it can't help me because it has no idea what I want. Being able to type "FROM table SELECT" would be way friendlier for humans, because IDEs could offer me immediately columns I'm very likely interested in.