Hacker News Reader

Comment by SSJPython | Hacker News Reader

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.

View on HN

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.

> SQLite also ignores size restrictions on text columns; [...] requires a trigger.

I'd go for CHECK constraints first: https://www.sqlite.org/lang_createtable.html#ckconst

> 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.

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.

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

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.

https://www.sqlite.org/datatype3.html

The one of the database you use. They all have their idiosyncrasies you’ll have to learn once you actually use them.

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.

Reply on news.ycombinator.com