7 ms·

I agree except one thing: SELECT cols FROM table. This should really be FROM table SELECT cols as every modern SQL editor will then be able to show you a list

by m_st 8y ago

I agree except one thing: SELECT cols FROM table. This should really be FROM table SELECT cols as every modern SQL editor will then be able to show you a list of available columns. See LINQ. There they had the opportunity to fix it and it works just great.

jrockway 8y ago
I mean, it works either way. Once you type the column names, it can guess the table name easily. I use DataGrip at work and it can complete the column names without knowing the table name, so it ends up not being a problem.
- dragonwriter 8y ago
  > Once you type the column names, it can guess the table name easily. If the query is simple enough that a natural join would work and you aren't doing FROM clause aliasing (which can be useful to make reusable queries self-documenting), sure. For more complex queries and obviously any time table or column aliasing are used, that becomes somewhere between less likely and logically impossible.
sqldba 8y ago
It’s also how azure query language works.
techno_modus 8y ago
I personally think first about the loop/iterator organization (FROM in SQL) and only after that what I am going to do with its elements. However, SELECT a1,a2,... FROM Table AS t WHERE Condition is syntactically equivalent to Python list comprehension: [(t.a1,t.a2,...) for t in Table if Condition] Here the use of attributes (SELECT) is also written before the iterator. SELECT is also analogous to normal loops: foreach t in Table if not Condition: continue # Use t.a1, t.a2 etc. Here we first provide the loop specification while the usage of elements is written only in the body.
lozzo 8y ago
I concur. I always start with select * from table because I cannot possibly remember all the right column names
piaste 8y ago
More generally, statements should appear in the same order as they are executed, as far as this is possible. This nullifies some other common sources of confusion, such as which statements are executed before a GROUP BY and which ones after (and why HAVING exists as a keyword). It's also similar to how CTE syntax is generally much more readable than subqueries.
- hesk 8y ago
  The order of clauses in a SQL statement should have no relation on the order of execution because SQL is a declarative query language. The query specifies what the user wants and not how the system should compute the answer. This declarative property also provides a (somewhat weak) motivation of why SELECT should come first. Precisely because it answers part of the WHAT, i.e., the schema of the result.
icc97 8y ago
I'd rather have the enjoyment of being able to think in normal English speak rather than Yoda speak just to fix one edge case that appeared decades after they came up with the syntax.
nexxer 8y ago
In the clients I've used (SQLYog, Mysql Workbench), aliasing a table lets you use autocomplete when referencing that alias followed by a dot. So, I will start with a SELECT * FROM myTable t, then go back and replace * with t.<columns appear here>. I'll use the same in other places in the query like WHERE conditions.

by m_st 8y ago

jrockway 8y ago

I mean, it works either way. Once you type the column names, it can guess the table name easily. I use DataGrip at work and it can complete the column names without knowing the table name, so it ends up not being a problem.

dragonwriter 8y ago
> Once you type the column names, it can guess the table name easily. If the query is simple enough that a natural join would work and you aren't doing FROM clause aliasing (which can be useful to make reusable queries self-documenting), sure. For more complex queries and obviously any time table or column aliasing are used, that becomes somewhere between less likely and logically impossible.

sqldba 8y ago

It’s also how azure query language works.

techno_modus 8y ago

I personally think first about the loop/iterator organization (FROM in SQL) and only after that what I am going to do with its elements. However, SELECT a1,a2,... FROM Table AS t WHERE Condition is syntactically equivalent to Python list comprehension: [(t.a1,t.a2,...) for t in Table if Condition] Here the use of attributes (SELECT) is also written before the iterator. SELECT is also analogous to normal loops: foreach t in Table if not Condition: continue # Use t.a1, t.a2 etc. Here we first provide the loop specification while the usage of elements is written only in the body.

lozzo 8y ago

I concur. I always start with select * from table because I cannot possibly remember all the right column names

piaste 8y ago

More generally, statements should appear in the same order as they are executed, as far as this is possible. This nullifies some other common sources of confusion, such as which statements are executed before a GROUP BY and which ones after (and why HAVING exists as a keyword). It's also similar to how CTE syntax is generally much more readable than subqueries.

hesk 8y ago
The order of clauses in a SQL statement should have no relation on the order of execution because SQL is a declarative query language. The query specifies what the user wants and not how the system should compute the answer. This declarative property also provides a (somewhat weak) motivation of why SELECT should come first. Precisely because it answers part of the WHAT, i.e., the schema of the result.

icc97 8y ago

I'd rather have the enjoyment of being able to think in normal English speak rather than Yoda speak just to fix one edge case that appeared decades after they came up with the syntax.

nexxer 8y ago

In the clients I've used (SQLYog, Mysql Workbench), aliasing a table lets you use autocomplete when referencing that alias followed by a dot. So, I will start with a SELECT * FROM myTable t, then go back and replace * with t.<columns appear here>. I'll use the same in other places in the query like WHERE conditions.