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.
The only other language like this I can think of is Rust. You can write r#keyword to quote a keyword to be used as an identifier.
This solution was devised as a means to add keywords to new editions of the language without breaking code written in the old edition that named this stuff the same as the keyword (and with full interoperability between code from new editions and old editions)
So if some function in some Rust 2015 library was named async (a new keyword introduced in Rust 2018), you can call it like r#async() in newer versions
To join the other commenters, in Nim you use backticks to allow using Nim keywords - this is important for e.g. nice deserialization where some JSON object has a field like `type` which is a keyword in Nim.
I can't. I forgot which stackoverflow comment or blog post mentioned it, but unquoted lowercase identifiers are the most portable and resilient naming convention that work across all database dialects. You don't have to worry about whether your database preserves case, folds everything to uppercase (Oracle) or folds everything to lowercase (Postgres) if you only stick with unquoted lowercase identifiers, and your SQL queries pretty much look the same between all databases as long as you stick with ANSI SQL.
Surely quoted identifiers are the most portable? If you quote everything you get to skip the entire normalisation issue, as well as the keywords issue.
You would think that right, but quoting an identifier is the start of misery. It means everytime you invoke the identifier, you have to quote it. If you don't quote it, you open yourself to bugs. It's a giant footgun. "But I'm quoting lowercase identifiers, so even if I forget the quotes it's fine". It's not. Only Postgres folds unquoted identifiers to lowercase. Oracle and SQL Server fold unquoted identifiers to uppercase. MySQL does the weird thing where it either folds to uppercase or preserves case sensitivity depending on whether you're running it on Windows or Unix (fun!). So not quoting your identifiers now means its behaviour is dependent on what database configuration you're using. It's not worth it. By using only lowercase unquoted identifiers, you can guarantee it behaves identically across every database, because even if they fold to uppercase or fold to lowercase or preserves case your lowercase unquoted identifiers get normalized accordingly to the database's rules and it all works without a hitch. Even if other people bring their weird database-isms like uppercasing identifiers and lowercasing keywords (like my company, sigh), it all works seamlessly. Say no to quoted identifiers, unless you want to saddle your developers with additional burden everytime they write an SQL query that touches the database.
Oh and yeah, every database brings its own opinion on what [quoting] `should` "look" 'like'.
I don't know how to reply to that except "experience tells me it is miserable to mandate everyone to quote their identifiers when they touch your database". Do you do that?
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)
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.
> 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-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.”
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.
2016/2023 got pretty widespread support across databases for the functionality I care about, both before and after adoption - namely the JSON stuff. MSSQL also already had pretty good graph support AFAIK, although I haven't used it that much.
I find the temporal table stuff really useful and they drastically simplify a number of requirements, so it's annoying that the only non-proprietary DB that supports it is maria.
The existing implementations (Oracle DB, SQL Server, MariaDB, Big Query) come with their problems too. I was a big fan of the new features when it came out in 2011, but pratically there is an unsolved elephant in the room: It doesn't cover schema changes.
> there is an unsolved elephant in the room: It doesn't cover schema changes.
100% agreed. It's remarkable how Datomic also arrived on the scene in the same era (2012) but actually managed to solve a lot of these hard issues of immutable versioning + schema evolution via a clean EAV-based information model and an emphasis on accrete-only schema changes.
While not the shit-show of bugs it was upon introduction in SQL Server 2008, there are still reasons to be careful with MERGE in SQL Server: it can still deadlock with itself in some circumstances, has issues with filtered indexes, can cause problems with CDC (wrong operation(s) get logged), …
(I actually like SQL Server, but the implementation of MERGE found there-in is certainly not one of the things I like about it!)
>You still need someone to review the generated code if it throws an error for potential data loss.
Yeah, exactly what I meant by the terraform-like plan/apply stages. You generate the change script and save it as your "plan", and then have an apply step that takes a backup and runs the script.
MSSQL is one of the best rdbms you can buy, the absolute best for some use cases. Its only downside is that it’s expensive. T-SQL idiosyncrasies are inconsequential.
I really gotta appreciate that someone's willing to say this out loud, rather than just pretending than MSSQL must be horrible because it comes from MS. If MSSQL was OSS Postgres wouldn't stand a chance.
Sorry, no. Not even close. Postgres is very good at what it can do and very cheap, but it just doesn’t do very much compared to SQL server or others. I’ve ran both mssql and Postgres in prod in the same project in perhaps not big but still nontrivial scale (low single digit terabytes) and SQL server made some things possible which Postgres simply didn’t have an answer for - column stores being the notable single killer feature if I had to name only one.
I'm currently running SQL Server and Postgres in the same project, and I would love to dump SQL Server. Its geospatial "capabilities" are so laughably bad, they should just stop advertising that they exist at all, and the unpredictable locking behavior alone should disqualify it from being used in any modern software project. And sure, it has column stores, which postgres has in extensions, but even if I needed column stores, SQL Server would still be last on my list, because there are so many better options out there.
Would not some variants of Sybase be better, as the architecture is the same, and it has supported far more platforms which allow it to scale in ways that Microsoft SQL Server cannot?
I’d been running couple TB mixed olap + oltp on 2016 a few years ago and a single cluster handled what otherwise today would be 3 or 4 different data stores today. Performance of properly tuned column stores was literally mind blowing. Haven’t even needed memory optimized tables.
Can’t comment on Sybase. Didn’t need any other platforms.
There were significant under-the-hood rewrites for SQL Server 2005 at least, and no doubt many smaller changes throughout the versions even where not adding new features, though there is still plenty that remains the same or at least very similar. I can name one bug from way-back-then which will never be fixed because maybe something somewhere “relies” on the odd behaviour!
It is one of the better ones out there. I shy away from it because of cost. If it was similar in cost to the other ones then I would probably use it a lot more. Been using it from when it was watcom (for about 50 to 250 bucks a copy). Then MS took over and the cost went crazy not oracle crazy but crazy enough.
WITH [WITH] AS (
SELECT [AS], [IN], [ON]
FROM [UNION]
WHERE [AND] = [OR]
),
[OUTER] AS (
SELECT [LEFT], [RIGHT], [FULL]
FROM [CROSS]
WHERE [INNER] = [OUTER]
),
[GROUP] AS (
SELECT COUNT(*) AS [HAVING]
FROM [ORDER]
GROUP BY [GROUP]
HAVING COUNT(*) > 1
)
SELECT
[WITH].[AS],
[OUTER].[LEFT],
[GROUP].[HAVING]
FROM
[WITH]
JOIN
[OUTER]
ON
[WITH].[IN] = [OUTER].[RIGHT]
LEFT JOIN
[GROUP]
ON
[WITH].[ON] = [GROUP].[HAVING]
WHERE
[WITH].[AS] = [OUTER].[LEFT]
AND
([GROUP].[HAVING] IS NULL OR [GROUP].[HAVING] > 1)
ORDER BY
[WITH].[AS] ASC;
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/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.
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.
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.
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).
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.
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.
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.
"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).
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.
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.
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].
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'"?
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.
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.
The one that you are currently using is the best one to learn. They're all different and they all work slightly differently. You need to learn generalities, not specifics, until you're working with a specific RDBMS.
SQLite is fine. The biggest issue to be aware of with SQLite is that SQLite's type affinity system is completely different from how other SQL RDBMSs function. The norm is for columns to have much more rigid data typing.
SQLite also ignores size restrictions on text columns; trying to get CHAR(2) to prevent the insertion of ILLINOIS instead of IL requires a trigger. Most other databases do not behave like this.
Other databases all had wonky left join syntax before the SQL92 standard - every attempt should be made to avoid archaic syntax if possible. SQLite itself lacked right join until recently.
Procedural SQL comes in two common varieties - ANSI SQL/PSM (strongly influenced by Oracle), and Transact-SQL (that is only found on Sybase and Microsoft SQL Server). Choose your investment here carefully.
> Procedural SQL [...] Choose your investment here carefully.
I think that the demand for procedual code has dropped drastically in the past decades as the "normal" SQL can solve so many more things with window functions, recursion, and so forth. So I'd say: Yes, choose your investment wisely and stay away from procedural SQL as long as possible.
There is still a case to embed business logic at the database layer, not the application layer, as databases tend not to change as much.
I have thousands of lines of PL/SQL that originated in the days of PowerBuilder that are now serviced by .NET; a decade from now could be totally different.
SQLite appears to use a (very small) subset of PSM for triggers.
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), which is less immediately problematic, but is still not great, especially for whoever needs to maintain that code.
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)
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.
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.
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.
One thing to keep in mind: the type system is extremely unusual even in simple cases compared to what I'd expect in a SQL-92 system.
It's clever, giving SQLite a lot of power in limited code, important in its conventional application in embedded use cases where fixed costs like code object size and starting database heap size are pretty constrained, and databases tend to be small...but, nevertheless, it's in its own world, there.
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.
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.
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.
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.
Then enable a spell checker in the editor / ide in which you write your SQL. Not being a native speaker should never be used as an argument against proper naming and wording of code and or documentation. I am not a native English speaker as well.
As pointed out by other replies to your comment. Abbreviations can lead to similar problems even with native speakers.
Sorry ESL here is "English as a Second Language" - or non-native english speakers. Our company has a diverse set of employees and only about 60% of us have English as our native language.
[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.