11 ms·

SQL reserved words – An empirical list

177 points by mariuz 3y ago

user3939382 3y ago
I don't know how many are missing but I'm guessing several since I didn't see one: "TYPE" https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-T https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywor... MySQL's SQL is the only language I use where I regularly trip on reserved keywords, super annoying. Not sure off the top of my head if there's some better design to fix that short of namespacing every reserved keyword. In some cases you can do `` to get around it but better to avoid them altogether.
- masklinn 3y ago
  "TYPE" is a non-reserved keyword (hence the lack of an (R) in your link), it is also non-reserved in standard SQL. So it's only a keyword in some contexts, and it's generally available as an identifier. From the wording of TFA, it probably only lists reserved keywords. create table type ( type integer ); insert into type (type) values (1), (2); select type from type; works perfectly fine in all of sqlite, postgres, mysql, and sql server (at least according to onecompiler.com).
  - user3939382 3y ago
    I see. I don’t make a distinction between reserved and keyword I guess as a matter of habit. It seems like asking for trouble. To use the type example you provided, I’ve seen that example break syntax highlighting and ORMs where you need special quoting to make it work.
- rgreasons 3y ago
  Having to quote escape a “timestamp” field at least once a week, I was _extremely_ surprised to see it excluded from this list.
  - MarkusWinand 3y ago
    Fair point, looks suspicious on first sight. I've checked my tests briefly: It's accepted as table and column name. As mentioned in footnote [0] I haven't checked different contextes yet. Can you tell me: what kind of identifier is it (view name, function name) and which SQL context it causes problems (select list, create/drop statement, ...) and which system has problems with it. Thx.
SSJPython 3y ago
What's the most useful "dialect" of SQL one should learn? I've only learned SQLite but there are just so many that it's heard to know which one is most in demand.
- at_a_remove 3y ago
  Honestly, you picked the right one. It's restricted, so what you learn will be widely applicable. Do yourself a favor and pick up some books about SQL by Joe Celko's SQL Puzzles and Answers. If you follow it, you will learn how to accomplish various queries while having restrictions on dialects or whatever. It's a real mind-expander. I found myself doing in SQLite things I hadn't thought possible for that set of keywords.
- EForEndeavour 3y ago
  This might reveal my technical mediocrity, but my approach: 1. Write the SQL that I think should mostly work 2. Try to run it 3. Fix errors with the help of docs, Stack Overflow, and now generative AI 4. Subconsciously learn whatever dialect this is to improve my performance on Step 1
  - gvx 3y ago
    This might work in the short term, but I don't recommend it as a general technique. This sort of an approach often causes there to be hidden bugs, which do not generate errors now, but will cause some problem down the line. It also has a tendency to lead to cargo cult programming (https://en.wikipedia.org/wiki/Cargo_cult_programming https://en.wikipedia.org/wiki/Cargo_cult_programming), which is less immediately problematic, but is still not great, especially for whoever needs to maintain that code.
- simonw 3y ago
  SQLite is a really good one because - at least for SELECT features - it feels to me like a very clean subset that works across most other databases. I'd recommend digging into PostgreSQL as well, since it's "larger" than SQLite and will expose you to a bunch more concepts. If you're familiar with both SQLite and PostgreSQL you should find other dialects very easy to pick up when you need them.
em500 3y ago
Quoting the SQLite docs: The SQL standard specifies a large number of keywords which may not be used as the names of tables, indices, columns, databases, user-defined functions, collations, virtual table modules, or any other named object. The list of keywords is so long that few people can remember them all. For most SQL code, your safest bet is to never use any English language word as the name of a user-defined object. https://www.sqlite.org/lang_keywords.html https://www.sqlite.org/lang_keywords.html
- masklinn 3y ago
  For increased complexity, SQL actually has two kinds of keywords, reserved and non-reserved, non-reserved is what some grammars call contextual or lexical keywords: they are only keywords in specific contexts, but are generally usable as unquoted identifiers. And obviously this is only for maximally portable SQL, postgres has a comparative table, which demonstrates that many of SQL's reserved keywords are non-reserved in postgres-sql: https://www.postgresql.org/docs/current/sql-keywords-appendix.html https://www.postgresql.org/docs/current/sql-keywords-appendi...
- stronglikedan 3y ago
  This is exactly why I prefer to use the plural forms for table names, despite the debate.
  - munk-a 3y ago
    Curse you - it's an apple collection not a collection of apples! I loathe plurality in table names because it's hard for ESL developers due to the plethora of irregular pluralities (like the word pluralities) in english. If object/entity names and table names are both singular you can do a lot of stuff on autopilot.
    - kreeben 3y ago
      Electronic Sports League? Anywho, I agree, you see: "It puts the lotion in the basket" See? Not a plural in sight!
dspillett 3y ago
> Note that you can still use these words as identifiers by putting them under double quotes ("). Of surrounding by square brackets in SQL Server, like [so] instead of like "so", which despite being non-standard is more commonly used in the MS SQL world because its behaviour is much more consistent than quotes in that environment. Quoted (rather than bracketed) identifiers are supported but it depends upon a setting which may vary per DB or procedure. It is common to see the option ON these days a some features¹ depend upon it, and many tools like MS's SSMS default it to ON, but this can not at all be relied upon. See https://learn.microsoft.com/en-us/sql/t-sql/statements/set-quoted-identifier-transact-sql https://learn.microsoft.com/en-us/sql/t-sql/statements/set-q... -- [1] From the documentation: “SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, then CREATE, UPDATE, INSERT, and DELETE statements will fail on tables with indexes on computed columns, or tables with indexed views.”
- darksaints 3y ago
  Thank you for the regularly scheduled reminder as to why nobody should ever use MS SQL for any reason whatsoever.
  - rjbwork 3y ago
    Until any other RDBMS has the combination of now-basic features like SQL:2011, declarative schema design with terraform-like plan/apply migrations (ssdt, azure data studio, DACPAC, etc.), easily configurable hosted cloud databases, actual good MERGE syntax, and more, I'll keep using it. Attempting to use Maria was a nightmare. I'd like Postgres more (and really want to) if they would at least implement more basic stuff like SQL:2011 and/or there was a good hosted deploy story - RDS/Aurora is fairly complex.
    - Varriount 3y ago
      Could you expand on the declarative schema features? Isn't SQL more or less declarative by default?
layer8 3y ago
Oracle actually provides a database table listing all keywords, along with information about their respective type of reservedness: https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-RESERVED_WORDS.html https://docs.oracle.com/en/database/oracle/oracle-database/1...
cryptonector 3y ago
This is very nice.
zX41ZdbW 3y ago
ClickHouse has no reserved keywords, e.g. SELECT SELECT FROM FROM is a valid query.
- SpicyLemonZest 3y ago
  I'm confused how their parser could support that. Isn't `SELECT SELECT FROM FROM foo` ambigous between "get the column 'SELECT' from the table 'FROM' aliased as 'foo'" and "get the column 'SELECT' aliased as 'FROM' from the table 'foo'"?
  - zX41ZdbW 3y ago
    Unquoted alias without the AS keyword cannot be named FROM.
    - deleted 3y ago
      [deleted]
- gkbrk 3y ago
  You can make it even funnier. create table from (select UInt8, where UInt8) engine = MergeTree() order by tuple(); insert into from values (1, 1), (2, 1), (3, 0), (4, 0), (5, 1); select select from from where where; select select from from where where is a really lovely query.
timcavel 3y ago
[dead]
ketralnis 3y ago
Maybe allowing bare words as both identifiers and keywords that could occur in the same position was never the right solution. SQL's roots start young in programming language design so no shame on anybody for not knowing. But e.g. if you're developing an ORM today that generates SQL strings and that SQL dialect might change in a DB upgrade later you should just be always quoting user identifiers.
- tsimionescu 3y ago
  Is there any programming language which doesn't have this property? All of the mainstream ones certainly use bare words as both keywords and identifiers, and there can sometimes be ambiguity. Most don't even have a solution like quoted identifiers (though quite a few do).
  - ketralnis 3y ago
    Forth and Lisp? :) I'll admit that I'm not an expert here but to my eye it's a combination of bare words and cases where the position might be amgiuous, combined with the range of dialects where your reserved words could even change in the future. `select distinct` could be `select [distinct]` or just `select distinct`. Compare to say C where `sometype somebareword` isn't ambiguous because `sometype` is always a language keyword. But wait you say, with typedefs sometype could well be a `typedef stuct somebareword! Yep, it's a problem here too and I've been lying all along. So that's some rambling but I think you're right, it's just a problem in SQL because of the range of dialects and the fact that they can change out from under you.
    - tsimionescu 3y ago
      > Forth and Lisp? :) Not having keywords at all is cheating :) Or, for Common Lisp at least, perhaps we can say that (,), #, ;, ", `,', :, commas, whitespace, and numbers are actually the keywords, since most other ASCII chars are valid identifiers but these aren't (so you can have a variable named = or ?, but you can't have a variable named # or 1. And in that case, CL also mixes up "keywords" and identifiers just like everyone else :) . > Compare to say C where `sometype somebareword` isn't ambiguous because `sometype` is always a language keyword. But wait you say, with typedefs sometype could well be a `typedef stuct somebareword! Yep, it's a problem here too and I've been lying all along. Even without typedefs, you have a problem more closely related to "select x" VS "select distinct x" - you can have "long x;" or "long double x;". Either way, yes, I think we agree - there's a difference in quantity and stability of keywords with SQL, but not much else.
kristopolous 3y ago
Do they have tests for this? was it done manually?
- MarkusWinand 3y ago
  Author of the website here. All charts on modern-sql.com are backed by tests. I also keep them up-to-date by running them for new releases when they appear.
  - momirlan 3y ago
    we xan always rely on you . thank you Markus
Fernicia 3y ago
Darn. I really wanted to name my Db2 table "END-EXEC".
- deleted 3y ago
  [deleted]
SPBS 3y ago
SQL's most egregious reserved keyword is, in my opinion, "user". Almost every application will have a users table. I gravitate towards singular table names these days [1], and SQL sitting its fat behind on the word "user" means I always have to defer to "users" just for the users table. There is almost no other keyword that I regularly run into conflict with other than "user". I don't even use the "user" keyword in any SQL queries, what a terrible trade off. [1] I used to prefer plural names, but now I favor singular names because no naming headaches with the plural intricacies of the English language.
- reportgunner 3y ago
  What word would you pick instead of "USER" for users in the context of DBMS ?
  - SPBS 3y ago
    I just name it users and accept the minor inconsistency that every table is singular except for the users table (it has the lowest cognitive overhead IMO)
    - reportgunner 3y ago
      No, I mean the word USER in a statement like below CREATE USER user_name [ { FOR | FROM } LOGIN login_name ] [ WITH <limited_options_list> [ ,... ] ] [ ; ]
      - yencabulator 3y ago
        It's a shame that the classic style separation of lexing and parsing means to support the command CREATE USER FOR LOGIN WITH means all of those are now magic keywords, instead of only behaving so in the context of a CREATE command. As for how to name the "user" table, I've used "account". User is a human not a piece of data, Account is the data I have about their association with my service.
- dpifke 3y ago
deergomoo 3y ago
It's fun when these change between versions too. At work we recently upgraded a bunch of servers from MySQL 5.7 to 8.0 (yeah I know, basically last second) and missed a stored procedure that was using "rows" unquoted. Fine in 5.7; reserved as of 8.0.2. I also find their documentation on this sort of thing to be far too sprawled to be easily accessible, makes it quite hard to prepare for this sort of thing if you're not particularly expert when it comes to database administration.
darigo 3y ago
Not strictly related by I really enjoy using SQL as a general-purpose programming language for coding challenges. Here's my n queens solution in SQL, for example: https://gist.github.com/seisvelas/952185983a625cd16e1ed4d9019cf0e5 https://gist.github.com/seisvelas/952185983a625cd16e1ed4d901.... I made that because I ran out of challenges on CodeWars. implementing algorithms in SQL really made my intuition for SQL skyrocket, to the point where no tasks in my day to day data engineering role were very challenging - at least not in terms of dealing with crazy joins and subqueries and such. I miss SQL now days (as an appsec dev). Currently, I'm learning Hoon, which is too intuitive to really replace SQL as a language for doing toy problems while having to think wierd.
- czscout 3y ago
  Wow, that n queens implementation is super impressive! I firmly fall into the first category of SQL knowledge, but I definitely know enough to be impressed.
- chunkyks 3y ago
  My pet silliness in sql from a couple years ago: https://github.com/chunky/sqlraytracer https://github.com/chunky/sqlraytracer You remind me I need to finish my hunt the wumpus implementation. (sqlite has an easy way to get user input, mid query...)
NoNotTheDuo 3y ago
I'm surprised sqlfluff hasn't been mentioned yet. Perhaps not a comprehensive list, but it's worked for everything I've thrown at it. There's an ANSI keyword list [0], and then dialect-specific lists for everything from DB2 [1] to Snowflake [2]. [0]: https://github.com/sqlfluff/sqlfluff/blob/main/src/sqlfluff/dialects/dialect_ansi_keywords.py https://github.com/sqlfluff/sqlfluff/blob/main/src/sqlfluff/... [1]: https://github.com/sqlfluff/sqlfluff/blob/main/src/sqlfluff/dialects/dialect_db2_keywords.py https://github.com/sqlfluff/sqlfluff/blob/main/src/sqlfluff/... [2]: https://github.com/sqlfluff/sqlfluff/blob/main/src/sqlfluff/dialects/dialect_snowflake_keywords.py https://github.com/sqlfluff/sqlfluff/blob/main/src/sqlfluff/...
jve 3y ago
Hey, SQL Server CASE should be as reserved. https://learn.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/t-sql/language-element...

177 points by mariuz 3y ago

I don't know how many are missing but I'm guessing several since I didn't see one: "TYPE" https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-T https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywor... MySQL's SQL is the only language I use where I regularly trip on reserved keywords, super annoying. Not sure off the top of my head if there's some better design to fix that short of namespacing every reserved keyword. In some cases you can do `` to get around it but better to avoid them altogether.

masklinn 3y ago
"TYPE" is a non-reserved keyword (hence the lack of an (R) in your link), it is also non-reserved in standard SQL. So it's only a keyword in some contexts, and it's generally available as an identifier. From the wording of TFA, it probably only lists reserved keywords. create table type ( type integer ); insert into type (type) values (1), (2); select type from type; works perfectly fine in all of sqlite, postgres, mysql, and sql server (at least according to onecompiler.com).
- user3939382 3y ago
  I see. I don’t make a distinction between reserved and keyword I guess as a matter of habit. It seems like asking for trouble. To use the type example you provided, I’ve seen that example break syntax highlighting and ORMs where you need special quoting to make it work.
rgreasons 3y ago
Having to quote escape a “timestamp” field at least once a week, I was _extremely_ surprised to see it excluded from this list.
- MarkusWinand 3y ago
  Fair point, looks suspicious on first sight. I've checked my tests briefly: It's accepted as table and column name. As mentioned in footnote [0] I haven't checked different contextes yet. Can you tell me: what kind of identifier is it (view name, function name) and which SQL context it causes problems (select list, create/drop statement, ...) and which system has problems with it. Thx.

SSJPython 3y ago

What's the most useful "dialect" of SQL one should learn? I've only learned SQLite but there are just so many that it's heard to know which one is most in demand.

at_a_remove 3y ago
Honestly, you picked the right one. It's restricted, so what you learn will be widely applicable. Do yourself a favor and pick up some books about SQL by Joe Celko's SQL Puzzles and Answers. If you follow it, you will learn how to accomplish various queries while having restrictions on dialects or whatever. It's a real mind-expander. I found myself doing in SQLite things I hadn't thought possible for that set of keywords.
EForEndeavour 3y ago
This might reveal my technical mediocrity, but my approach: 1. Write the SQL that I think should mostly work 2. Try to run it 3. Fix errors with the help of docs, Stack Overflow, and now generative AI 4. Subconsciously learn whatever dialect this is to improve my performance on Step 1
- gvx 3y ago
  This might work in the short term, but I don't recommend it as a general technique. This sort of an approach often causes there to be hidden bugs, which do not generate errors now, but will cause some problem down the line. It also has a tendency to lead to cargo cult programming (https://en.wikipedia.org/wiki/Cargo_cult_programming https://en.wikipedia.org/wiki/Cargo_cult_programming), which is less immediately problematic, but is still not great, especially for whoever needs to maintain that code.
simonw 3y ago
SQLite is a really good one because - at least for SELECT features - it feels to me like a very clean subset that works across most other databases. I'd recommend digging into PostgreSQL as well, since it's "larger" than SQLite and will expose you to a bunch more concepts. If you're familiar with both SQLite and PostgreSQL you should find other dialects very easy to pick up when you need them.

em500 3y ago

Quoting the SQLite docs: The SQL standard specifies a large number of keywords which may not be used as the names of tables, indices, columns, databases, user-defined functions, collations, virtual table modules, or any other named object. The list of keywords is so long that few people can remember them all. For most SQL code, your safest bet is to never use any English language word as the name of a user-defined object. https://www.sqlite.org/lang_keywords.html https://www.sqlite.org/lang_keywords.html

masklinn 3y ago
For increased complexity, SQL actually has two kinds of keywords, reserved and non-reserved, non-reserved is what some grammars call contextual or lexical keywords: they are only keywords in specific contexts, but are generally usable as unquoted identifiers. And obviously this is only for maximally portable SQL, postgres has a comparative table, which demonstrates that many of SQL's reserved keywords are non-reserved in postgres-sql: https://www.postgresql.org/docs/current/sql-keywords-appendix.html https://www.postgresql.org/docs/current/sql-keywords-appendi...
stronglikedan 3y ago
This is exactly why I prefer to use the plural forms for table names, despite the debate.
- munk-a 3y ago
  Curse you - it's an apple collection not a collection of apples! I loathe plurality in table names because it's hard for ESL developers due to the plethora of irregular pluralities (like the word pluralities) in english. If object/entity names and table names are both singular you can do a lot of stuff on autopilot.
  - kreeben 3y ago
    Electronic Sports League? Anywho, I agree, you see: "It puts the lotion in the basket" See? Not a plural in sight!

dspillett 3y ago

> Note that you can still use these words as identifiers by putting them under double quotes ("). Of surrounding by square brackets in SQL Server, like [so] instead of like "so", which despite being non-standard is more commonly used in the MS SQL world because its behaviour is much more consistent than quotes in that environment. Quoted (rather than bracketed) identifiers are supported but it depends upon a setting which may vary per DB or procedure. It is common to see the option ON these days a some features¹ depend upon it, and many tools like MS's SSMS default it to ON, but this can not at all be relied upon. See https://learn.microsoft.com/en-us/sql/t-sql/statements/set-quoted-identifier-transact-sql https://learn.microsoft.com/en-us/sql/t-sql/statements/set-q... -- [1] From the documentation: “SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, then CREATE, UPDATE, INSERT, and DELETE statements will fail on tables with indexes on computed columns, or tables with indexed views.”

darksaints 3y ago
Thank you for the regularly scheduled reminder as to why nobody should ever use MS SQL for any reason whatsoever.
- rjbwork 3y ago
  Until any other RDBMS has the combination of now-basic features like SQL:2011, declarative schema design with terraform-like plan/apply migrations (ssdt, azure data studio, DACPAC, etc.), easily configurable hosted cloud databases, actual good MERGE syntax, and more, I'll keep using it. Attempting to use Maria was a nightmare. I'd like Postgres more (and really want to) if they would at least implement more basic stuff like SQL:2011 and/or there was a good hosted deploy story - RDS/Aurora is fairly complex.
  - Varriount 3y ago
    Could you expand on the declarative schema features? Isn't SQL more or less declarative by default?

layer8 3y ago

Oracle actually provides a database table listing all keywords, along with information about their respective type of reservedness: https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-RESERVED_WORDS.html https://docs.oracle.com/en/database/oracle/oracle-database/1...

cryptonector 3y ago

This is very nice.

zX41ZdbW 3y ago

ClickHouse has no reserved keywords, e.g. SELECT SELECT FROM FROM is a valid query.

SpicyLemonZest 3y ago
I'm confused how their parser could support that. Isn't `SELECT SELECT FROM FROM foo` ambigous between "get the column 'SELECT' from the table 'FROM' aliased as 'foo'" and "get the column 'SELECT' aliased as 'FROM' from the table 'foo'"?
- zX41ZdbW 3y ago
  Unquoted alias without the AS keyword cannot be named FROM.
  - deleted 3y ago
    [deleted]
gkbrk 3y ago
You can make it even funnier. create table from (select UInt8, where UInt8) engine = MergeTree() order by tuple(); insert into from values (1, 1), (2, 1), (3, 0), (4, 0), (5, 1); select select from from where where; select select from from where where is a really lovely query.

timcavel 3y ago

[dead]

ketralnis 3y ago

Maybe allowing bare words as both identifiers and keywords that could occur in the same position was never the right solution. SQL's roots start young in programming language design so no shame on anybody for not knowing. But e.g. if you're developing an ORM today that generates SQL strings and that SQL dialect might change in a DB upgrade later you should just be always quoting user identifiers.

tsimionescu 3y ago
Is there any programming language which doesn't have this property? All of the mainstream ones certainly use bare words as both keywords and identifiers, and there can sometimes be ambiguity. Most don't even have a solution like quoted identifiers (though quite a few do).
- ketralnis 3y ago
  Forth and Lisp? :) I'll admit that I'm not an expert here but to my eye it's a combination of bare words and cases where the position might be amgiuous, combined with the range of dialects where your reserved words could even change in the future. `select distinct` could be `select [distinct]` or just `select distinct`. Compare to say C where `sometype somebareword` isn't ambiguous because `sometype` is always a language keyword. But wait you say, with typedefs sometype could well be a `typedef stuct somebareword! Yep, it's a problem here too and I've been lying all along. So that's some rambling but I think you're right, it's just a problem in SQL because of the range of dialects and the fact that they can change out from under you.
  - tsimionescu 3y ago
    > Forth and Lisp? :) Not having keywords at all is cheating :) Or, for Common Lisp at least, perhaps we can say that (,), #, ;, ", `,', :, commas, whitespace, and numbers are actually the keywords, since most other ASCII chars are valid identifiers but these aren't (so you can have a variable named = or ?, but you can't have a variable named # or 1. And in that case, CL also mixes up "keywords" and identifiers just like everyone else :) . > Compare to say C where `sometype somebareword` isn't ambiguous because `sometype` is always a language keyword. But wait you say, with typedefs sometype could well be a `typedef stuct somebareword! Yep, it's a problem here too and I've been lying all along. Even without typedefs, you have a problem more closely related to "select x" VS "select distinct x" - you can have "long x;" or "long double x;". Either way, yes, I think we agree - there's a difference in quantity and stability of keywords with SQL, but not much else.

kristopolous 3y ago

Do they have tests for this? was it done manually?

MarkusWinand 3y ago
Author of the website here. All charts on modern-sql.com are backed by tests. I also keep them up-to-date by running them for new releases when they appear.
- momirlan 3y ago
  we xan always rely on you . thank you Markus

Fernicia 3y ago

Darn. I really wanted to name my Db2 table "END-EXEC".

deleted 3y ago
[deleted]

SPBS 3y ago

SQL's most egregious reserved keyword is, in my opinion, "user". Almost every application will have a users table. I gravitate towards singular table names these days [1], and SQL sitting its fat behind on the word "user" means I always have to defer to "users" just for the users table. There is almost no other keyword that I regularly run into conflict with other than "user". I don't even use the "user" keyword in any SQL queries, what a terrible trade off. [1] I used to prefer plural names, but now I favor singular names because no naming headaches with the plural intricacies of the English language.

reportgunner 3y ago
What word would you pick instead of "USER" for users in the context of DBMS ?
- SPBS 3y ago
  I just name it users and accept the minor inconsistency that every table is singular except for the users table (it has the lowest cognitive overhead IMO)
  - reportgunner 3y ago
    No, I mean the word USER in a statement like below CREATE USER user_name [ { FOR | FROM } LOGIN login_name ] [ WITH <limited_options_list> [ ,... ] ] [ ; ]
    - yencabulator 3y ago
      It's a shame that the classic style separation of lexing and parsing means to support the command CREATE USER FOR LOGIN WITH means all of those are now magic keywords, instead of only behaving so in the context of a CREATE command. As for how to name the "user" table, I've used "account". User is a human not a piece of data, Account is the data I have about their association with my service.
dpifke 3y ago

deergomoo 3y ago

It's fun when these change between versions too. At work we recently upgraded a bunch of servers from MySQL 5.7 to 8.0 (yeah I know, basically last second) and missed a stored procedure that was using "rows" unquoted. Fine in 5.7; reserved as of 8.0.2. I also find their documentation on this sort of thing to be far too sprawled to be easily accessible, makes it quite hard to prepare for this sort of thing if you're not particularly expert when it comes to database administration.

darigo 3y ago

Not strictly related by I really enjoy using SQL as a general-purpose programming language for coding challenges. Here's my n queens solution in SQL, for example: https://gist.github.com/seisvelas/952185983a625cd16e1ed4d9019cf0e5 https://gist.github.com/seisvelas/952185983a625cd16e1ed4d901.... I made that because I ran out of challenges on CodeWars. implementing algorithms in SQL really made my intuition for SQL skyrocket, to the point where no tasks in my day to day data engineering role were very challenging - at least not in terms of dealing with crazy joins and subqueries and such. I miss SQL now days (as an appsec dev). Currently, I'm learning Hoon, which is too intuitive to really replace SQL as a language for doing toy problems while having to think wierd.

czscout 3y ago
Wow, that n queens implementation is super impressive! I firmly fall into the first category of SQL knowledge, but I definitely know enough to be impressed.
chunkyks 3y ago
My pet silliness in sql from a couple years ago: https://github.com/chunky/sqlraytracer https://github.com/chunky/sqlraytracer You remind me I need to finish my hunt the wumpus implementation. (sqlite has an easy way to get user input, mid query...)

NoNotTheDuo 3y ago

I'm surprised sqlfluff hasn't been mentioned yet. Perhaps not a comprehensive list, but it's worked for everything I've thrown at it. There's an ANSI keyword list [0], and then dialect-specific lists for everything from DB2 [1] to Snowflake [2]. [0]: https://github.com/sqlfluff/sqlfluff/blob/main/src/sqlfluff/dialects/dialect_ansi_keywords.py https://github.com/sqlfluff/sqlfluff/blob/main/src/sqlfluff/... [1]: https://github.com/sqlfluff/sqlfluff/blob/main/src/sqlfluff/dialects/dialect_db2_keywords.py https://github.com/sqlfluff/sqlfluff/blob/main/src/sqlfluff/... [2]: https://github.com/sqlfluff/sqlfluff/blob/main/src/sqlfluff/dialects/dialect_snowflake_keywords.py https://github.com/sqlfluff/sqlfluff/blob/main/src/sqlfluff/...

jve 3y ago

Hey, SQL Server CASE should be as reserved. https://learn.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/t-sql/language-element...