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

Post by anon291 » Thu, Jan 25, 2024, 6:00 PM UTC

deepsun wrote: post #89

anon291 wrote: post #73

SQL is a mess. It's actually a fairly well-thought-out and straightforwards language. However, in the quest to make it 'human-readable', it's very unintuitive to compose.

Then let's stop using it for the wrong purpose. Any DBMS can have two interfaces -- SQL for human interaction, and proper API for machines to talk. More secure and efficient.

Post by rangerelf » Thu, Jan 25, 2024, 6:06 PM UTC

deepsun wrote: post #87

willvarfar wrote: post #79

Earlier quoted context omitted.

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…

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.

Post by mulmen » Thu, Jan 25, 2024, 6:14 PM UTC

zerocrates wrote: post #63

jjice wrote: post #3

Linq does this [0], maybe partially because it's SQL-like but built for working with enumerables instead so they could do what they wanted. One other quirk of SQL ordering that always gets me is that SET comes before WHERE in UPDATE. I always get terrified that I'll run without the WHERE or without selecting the WHERE. Thankfully a good database tool like DataGrip will yell at you if you try to do a modifying operati…

In terms of actually writing queries live, like if you're in the CLI client, the order of UPDATE is definitely terrifying. The workarounds of writing it out of order or as a SELECT first are fine... I'd almost like to see a mode the interactive client sets that just rejects any UPDATE without a WHERE, and you'd have to do WHERE 1 or similar to get an "UPDATE everything."

  /* use your PPE */
  BEGIN;
  /* make the change */
  UPDATE foo SET bar = ‘baz’;
  /* sanity check */
  SELECT * FROM foo WHERE bar != ‘baz’;
  /* oops, let’s pretend this never happened */
  ROLLBACK;

Post by rangerelf » Thu, Jan 25, 2024, 6:19 PM UTC

snarfy wrote: post #26

bigfudge wrote: post #15

Earlier quoted context omitted.

In English. I think other languages would have different word orderings. German for one, and I think maybe Russian too.

The second version is how you would say it in Spanish.

Post by Joker_vD » Thu, Jan 25, 2024, 6:31 PM UTC

setr wrote: post #94

kivle wrote: post #88

Earlier quoted context omitted.

I can't remember where I read it, but I believe one of the main reasons why Linq moved the FROM clause to the front was for better intellisense. If you start writing SELECT x in an IDE, there really is no way of providing intellisense for x. However if you write FROM table SELECT x, the IDE can first provide intellisense for table names while you write your FROM clause. Then it can provide intellisense for the SELECT…

I believe LINQ maps to a chains of function calls on an enumerable, with the functions usually taking anonymous functions as input (and then the series of functions is rewritten into a SQL statement by EF to hit the DB). I don’t think you’d be able to support type analysis if you didn’t specify the enumerable upfront Eg Mylist.select(x => …) can determine the type of x, because it has the type of mylist —> List Selec…

    select x => ... from mylist;

    mylist.SelectMany(x => ...);