4 ms·
In general, I love SQL. It's one of my favourite languages (or rather, language families) to play and tinker with. But the fact that SELECT comes before FROM ha
by ainar-g 7y ago
In general, I love SQL. It's one of my favourite languages (or rather,
language families) to play and tinker with. But the fact that SELECT
comes before FROM has always ground my gears. Thanks to this article, I
now finally have a better explanation than “Dunno, feels weird” :-)
- duxup 7y agoI always liked select first as it was "what do I want" then "given these rules". For some reason that made sense to me.
- ainar-g 7y agoI see your point, but FROM-first SQL would provide better support for autocomplete. Right now in e.g. psql when you write "SELECT " and press <Tab>, it can't help you with the columns. But if you could write "FROM users SELECT " and then press <Tab>, then the DB client could tell that you probably want a column of the "users" table.
- jfim 7y agoYou can use the WITH clause if you prefer that style. WITH employee AS (SELECT * FROM EMPLOYEES), department AS (SELECT * FROM DEPARTMENTS) SELECT employee.name, department.name FROM employee INNER JOIN department ON employee.department_id = department.department_id
- clhodapp 7y agoHow does that help at all, though? When it comes time to write the final select, you still haven't established the meanings of the things you are selecting from until you write the from and join clauses.
- jfim 7y agoYour IDE should be able to complete the columns at that point, so when it comes to typing SELECT employee.name, the IDE knows that name is a column of the EMPLOYEES table. This works with IntelliJ and Datagrip, I don't know about other IDEs.
- clhodapp 7y agoIt shouldn't, though, because there is no table bound to that name to draw from at that point. You haven't added a from-list that is actually relevant to the current scope yet.
- jfim 7y agoIt does work as described, with column type information and field suggestions, on IntelliJ 2019.2 and Datagrip 2019.2.
- clhodapp 7y agoI totally believe that it gives the suggestions as you describe, what I'm saying is that in order for it do that, the completion must be making faulty assumptions about the way that scoping works in SQL.
- deleted 7y ago[deleted]
- pdkl95 7y agoSELECT as the first keyword also makes sense as an opening indicator of the SQL statement's type. "Start a new task that SELECTs data" (then "what [data] do I want" then "given these rules") instead of INSERT, UPDATE, etc. From a source code structure perspective "SELECT foo ...;" sort of works like a function header: "function foo() { ... };"
- bradleybuda 7y ago+1. One of the few programming languages that allows you to "work backwards" from your desired result to the data / computation needed for that result.
- duxup 7y agoYeah I hadn't thought of it but otherwise when i go to make a thing happen i usually - 1. create a bunch of constructs to work towards that goal - - 2. work happens - - 3. finally get my output and have what i want -
- dragonwriter 7y agoSELECT before FROM makes sense when writing, especially fairly simple, queries in terms of designing results first. OTOH FROM first is better for tooling. Really, there's no really good reason for a mandatory order of clauses in an SQL statement.
- davidw 7y ago> SELECT before FROM makes sense when writing, especially fairly simple, queries in terms of designing results first. The thing I always imagine is a bunch of people in the 1970ies sitting around designing this stuff, and reading it out loud in a Captain Kirk voice "Computer, SELECT course WHERE Klingons IS zero!"
- mikestew 7y agoA long time ago, I might have forgiven such rigidness. But, yeesh, that was 30 years ago. In 2019 if I've already tokenized it for you with FROMs and GROUP BYs, you're a computer, go figure it out. Many years of writing SQL prevents me from but the most rare ordering mistake, but it kinda grinds my gears just a little that it's still an issue.
- jayd16 7y agoI agree but could you imagine the holy wars over the "proper" way to order a SQL query?
- devxpy 7y agoI wish it looked like the underlying relational algebra expression that it represents, but unfortunately, that's too much for your average joe with no compsci background.
- jbigelow76 7y agoI'm a big SQL fan too. Like the author, I kind of intuitively got the order after years and years of writing sql but if the syntax had evolved into FROM then SELECT I could get how it would make more sense. As it stands most of my queries start out: 1. "SELECT *" because habits 2. Start writing the FROM/JOINs and table aliases 3. Go back and refine the SELECT to tableAlias.ColumnName 4. (OPTIONAL) Aggregates 5. Forget the WHERE clause and burn the optimizer's cached query plans to the ground! (j/k, add WHERE or just throw a TOP 500 into the SELECT)