[HN Gopher] SQL Reserved Words - The Empirical List
___________________________________________________________________
SQL Reserved Words - The Empirical List
Author : mariuz
Score : 39 points
Date : 2023-10-11 13:45 UTC (2 hours ago)
(HTM) web link (modern-sql.com)
(TXT) w3m dump (modern-sql.com)
| user3939382 wrote:
| 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#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 wrote:
| "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).
| SSJPython wrote:
| 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.
| polygotdomain wrote:
| The short answer is to write ANSI 92 SQL and understand the
| variances between different RDBMS based on which ones you
| encounter.
|
| The longer answer is that some RBDMS adhere closer to the
| standard than others. Generally the more open source and more
| long lived a platform is, the closer to the standard it can be.
| However, even an RDBMS like MSSqlServer isn't that far off from
| it, and while it may have things it supports that are outside
| of that standard, it will still support ANSI SQL (i.e. `ISNULL`
| vs `COALESCE`)
|
| If you're looking for learning SQL that you can likely use in a
| wide number of places, I'd steer away from Oracle and DB2. Both
| are fairly proprietary, in my experience, and feel like writing
| in a different language that looks like SQL, but has a
| different set of rules and constraints.
| sbuttgereit wrote:
| SQLite isn't a bad one to know at all. It's pretty close to
| PostgreSQL and PostgreSQL prides itself as caring about the SQL
| standard.
|
| I do think that, as general learning experience, working with
| PostgreSQL is a good starting place because of the good degree
| of SQL standard compliance. Get those basics down and the less
| standards compliant vendors become more accessible.
|
| After that it depends what kinda of companies you'd want to
| work for. Enterprises deal much in MSSQL and Oracle. Start-uppy
| kinds of companies you're looking at PostgreSQL or MySQL... Or
| something not RDBMS at all. There are many generalizations that
| can be made but these are a few hand-wavy examples I would
| make.
| graypegg wrote:
| I don't work a ton in SQL, but my experience with SQLite tends
| to carry me thru anything that requires some SQL without having
| to commit to fully learning another dialect. I just pick up
| what I need when they differ. Some sort of IDE works great if
| it understands the dialects and will red-squiggle anything
| that's off. (DataGrip works great for me)
| at_a_remove wrote:
| 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.
| simonw wrote:
| 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.
| EForEndeavour wrote:
| 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
| dspillett wrote:
| Any one in detail, but be aware of the sort of differences
| found in the others.
|
| Which one should be your core one depends on what projects you
| wish to work on. My DayJob is an MS shop to SQL Server's TSQL
| is my area of expertise, but I know more-or-less what isn't
| supported or is handled differently in other common places
| (core postgres, mysql/mariadb, sqlite). In some places you may
| end up being more completely fluent in multiple rather than
| just one. The key is understanding the concepts (set based
| operations rather than thinking procedurally, recursive
| queries, window functions, how query planners commonly work so
| you can optimise for them) rather than specific syntax which is
| always easy to lookup.
| em500 wrote:
| 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
| masklinn wrote:
| 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-appendi...
| dspillett wrote:
| _> 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 features1 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-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."
| NoMoreNicksLeft wrote:
| select [select] from [from] where [where] = [equals]
|
| ?
| darksaints wrote:
| Thank you for the regularly scheduled reminder as to why nobody
| should ever use MS SQL for any reason whatsoever.
___________________________________________________________________
(page generated 2023-10-11 16:00 UTC)