4 ms·

The syntax and grammar of SQL commands do not have an elegant symmetry. Examples: The ordering is not always name of table and then names of columns... Write

by jasode 7y ago

The syntax and grammar of SQL commands do not have an elegant symmetry. Examples: The ordering is not always name of table and then names of columns... Write data: UPDATE customer SET membership_plan='PLATINUM' Read data (incorrect syntax if specifying table name then column name): SELECT customer GET membership_plan -- incorrect Read data (correct syntax of columns then table): SELECT membership_plan FROM customer For UPDATE, the column names are adjacent to the value with the '=' equals sign. However the INSERT statement splits column names away from the values; all column names as one delimited list in parentheses and then all the values are another delimited list: UPDATE customer SET membership_plan='PLATINUM', timezone='UTC' INSERT INTO customer (membership_plan,timezone) values ('PLATINUM','UTC') Prepositions... Prepositions before table name: SELECT FROM t, INSERT INTO t, DELETE FROM t No preposition before table name: UPDATE t For syntax consistency, the UPDATE would also have had a preposition such as "UPDATE TO t", or "UPDATE ON t", or "UPDATE ONTO t" ... or ... the other 3 commands would have removed the need for "FROM/INTO". Either way, all 4 SUDI commands could have looked more alike. Yes, one eventually gets used to the cosmetic inconsistencies but it's annoying for beginners because memorizing 1 of the 4 commands doesn't really reward you with the ability to predict the grammar of the other 3 commands. You have to learn the different quirks of the 4 commands. Maybe we should be grateful that SELECT/UPDATE/DELETE at least all happen to share the same "WHERE" clause. It seems like SQL's inconsistent syntax design should have tortured us with memorizing "WHERE" for one command and totally different synonyms such as "FILTER" and "CONDITION" for the others! [To downvoters, I'm willing to be persuaded that I'm wrong and that SQL syntax is actually "consistent" so please reply with your counterargument.]

grzm 7y ago
I agree with your overall theme: SQL is not composable. On the UPDATE front, some implementations (e.g., PostgreSQL) do allow tuple/row assignment, which does make things a bit nicer. (Small conveniences can make things better, while not perfect.) UPDATE customer SET (membership_plan, timezone) = ('PLATINUM', 'UTC") -- ... I tend to use row comparison and assignment where possible (in JOIN clauses, UPDATE statements, and WHERE clauses) to reinforce thinking in tuples, where a single column is the degenerate case (though I do then omit the surrounding parens).
shkkmo 7y ago
Mysql certainly has some syntactic warts, but you picked what I consider rather odd complaints. > The ordering is not always name of table and then names of columns I would point out that only SELECT (not INSERT, UPDATE, DELETE) can be run without providing a table reference. Also, the select doesn't take a simple list of column names, but is a more complicated tool for organizing and formatting data. (This section would not by syntactically or semantically similar to anything in the INSERT or UPDATE that is not itself part of a SELECT statement) > For UPDATE, the column names are adjacent to the value with the '=' equals sign. However the INSERT statement splits column names away from the values; all column names as one delimited list in parentheses and then all the values are another delimited list: UPDATE and INSERT both support the "assignment_list" syntax. So they are perfectly consistent. UPDATE also supports other formats (such as VALUES or SELECT). The real inconsistency I would have called our here is that while INSERT can be used with subqueries (via VALUES or with a SELECT clause), UPDATE must use joins to achieve a similar functionality. > For syntax consistency, the UPDATE would also have had a preposition such as "UPDATE TO t", or "UPDATE ON t", or "UPDATE ONTO t" ... or ... the other 3 commands would have removed the need for "FROM/INTO". Either way, all 4 SUDI commands could have looked more alike. Well "INTO" is an odd case, it is completely optional and serves no syntactic purpose. The FROM portion of SELECT statements does serve a syntactic purp0se but isn't actually required if it is not needed (E.G `SELECT "hello world"`). The inconsistent thing here is that the FROM portion of the DELETE syntax (which serves no syntactic purpose) is mandatory and not optional. > Maybe we should be grateful that SELECT/UPDATE/DELETE at least all happen to share the same "WHERE" clause. It seems like SQL's inconsistent syntax design should have tortured us with memorizing "WHERE" for one command and totally different synonyms such as "FILTER" and "CONDITION" for the others! If you look at the spec, SQL is pretty good at reusing syntactic units. You can see this in the documentation for both MySQL and Postgres. The worst thing in terms of SQL consistency is the syntactic and semantic differences between different flavors of SQL. This greatly reduces the value that SQL could have as a plug and play query format.
- jasode 7y ago
  >Mysql certainly has some syntactic warts, [...] UPDATE and INSERT both support the "assignment_list" syntax. So they are perfectly consistent. (I was making generic comments about SQL so not sure why you interpreted my post as criticizing MySQL.) Anyways an FYI for any readers not aware... MySQL has non-standard extensions[0] to the reference SQL language. Other databases like Oracle, MS SQL Server, and SQLite do not have INSERT with optional "assignment_list" syntax. >Well "INTO" is an odd case, it is completely optional Another example where MySQL is a special case because its proprietary extensions makes "INTO" optional. In other databases like SQLite, the "INTO" is absolutely required because omitting it in "INSERT customer" returns a syntax error. >I would point out that only SELECT (not INSERT, UPDATE, DELETE) can be run without providing a table reference. Yes, the ability for SELECT to leave out the table name is handy for functions like "SELECT date();" but its optionality would not have violated consistency with table-then-column ordering of INSERT & UPDATE. In cases where SELECT needs to specify table(s), it would have been more consistent grammar by putting the table names before the columns like INSERT & UPDATE. (This would have also greatly simplified editors with intellisense since putting the table first would let the parser know which columns to list as autocomplete suggestions. E.g. in Navicat SQL text editor, to get a list of autocomplete column names, I have to first type out "SELECT FROM customer" then move the cursor backwards to the left of "FROM" and then start typing "mem" to see "membership_plan" as an autocomplete suggestion.) [0] https://dev.mysql.com/doc/refman/8.0/en/extensions-to-ansi.html https://dev.mysql.com/doc/refman/8.0/en/extensions-to-ansi.h...

by jasode 7y ago

grzm 7y ago

I agree with your overall theme: SQL is not composable. On the UPDATE front, some implementations (e.g., PostgreSQL) do allow tuple/row assignment, which does make things a bit nicer. (Small conveniences can make things better, while not perfect.) UPDATE customer SET (membership_plan, timezone) = ('PLATINUM', 'UTC") -- ... I tend to use row comparison and assignment where possible (in JOIN clauses, UPDATE statements, and WHERE clauses) to reinforce thinking in tuples, where a single column is the degenerate case (though I do then omit the surrounding parens).

shkkmo 7y ago

Mysql certainly has some syntactic warts, but you picked what I consider rather odd complaints. > The ordering is not always name of table and then names of columns I would point out that only SELECT (not INSERT, UPDATE, DELETE) can be run without providing a table reference. Also, the select doesn't take a simple list of column names, but is a more complicated tool for organizing and formatting data. (This section would not by syntactically or semantically similar to anything in the INSERT or UPDATE that is not itself part of a SELECT statement) > For UPDATE, the column names are adjacent to the value with the '=' equals sign. However the INSERT statement splits column names away from the values; all column names as one delimited list in parentheses and then all the values are another delimited list: UPDATE and INSERT both support the "assignment_list" syntax. So they are perfectly consistent. UPDATE also supports other formats (such as VALUES or SELECT). The real inconsistency I would have called our here is that while INSERT can be used with subqueries (via VALUES or with a SELECT clause), UPDATE must use joins to achieve a similar functionality. > For syntax consistency, the UPDATE would also have had a preposition such as "UPDATE TO t", or "UPDATE ON t", or "UPDATE ONTO t" ... or ... the other 3 commands would have removed the need for "FROM/INTO". Either way, all 4 SUDI commands could have looked more alike. Well "INTO" is an odd case, it is completely optional and serves no syntactic purpose. The FROM portion of SELECT statements does serve a syntactic purp0se but isn't actually required if it is not needed (E.G `SELECT "hello world"`). The inconsistent thing here is that the FROM portion of the DELETE syntax (which serves no syntactic purpose) is mandatory and not optional. > Maybe we should be grateful that SELECT/UPDATE/DELETE at least all happen to share the same "WHERE" clause. It seems like SQL's inconsistent syntax design should have tortured us with memorizing "WHERE" for one command and totally different synonyms such as "FILTER" and "CONDITION" for the others! If you look at the spec, SQL is pretty good at reusing syntactic units. You can see this in the documentation for both MySQL and Postgres. The worst thing in terms of SQL consistency is the syntactic and semantic differences between different flavors of SQL. This greatly reduces the value that SQL could have as a plug and play query format.

jasode 7y ago
>Mysql certainly has some syntactic warts, [...] UPDATE and INSERT both support the "assignment_list" syntax. So they are perfectly consistent. (I was making generic comments about SQL so not sure why you interpreted my post as criticizing MySQL.) Anyways an FYI for any readers not aware... MySQL has non-standard extensions[0] to the reference SQL language. Other databases like Oracle, MS SQL Server, and SQLite do not have INSERT with optional "assignment_list" syntax. >Well "INTO" is an odd case, it is completely optional Another example where MySQL is a special case because its proprietary extensions makes "INTO" optional. In other databases like SQLite, the "INTO" is absolutely required because omitting it in "INSERT customer" returns a syntax error. >I would point out that only SELECT (not INSERT, UPDATE, DELETE) can be run without providing a table reference. Yes, the ability for SELECT to leave out the table name is handy for functions like "SELECT date();" but its optionality would not have violated consistency with table-then-column ordering of INSERT & UPDATE. In cases where SELECT needs to specify table(s), it would have been more consistent grammar by putting the table names before the columns like INSERT & UPDATE. (This would have also greatly simplified editors with intellisense since putting the table first would let the parser know which columns to list as autocomplete suggestions. E.g. in Navicat SQL text editor, to get a list of autocomplete column names, I have to first type out "SELECT FROM customer" then move the cursor backwards to the left of "FROM" and then start typing "mem" to see "membership_plan" as an autocomplete suggestion.) [0] https://dev.mysql.com/doc/refman/8.0/en/extensions-to-ansi.html https://dev.mysql.com/doc/refman/8.0/en/extensions-to-ansi.h...