Modern SQL

It's worth noting that ClickHouse supports both the standard SQL and the extensions.

For example, the standard SQL way to write filtered aggregates is:

SELECT avg(number) FILTER (WHERE number > 50) FROM numbers(100)

See https://modern-sql.com/feature/filter

But it is only supported by SQLite, PostgreSQL, and ClickHouse. While ClickHouse also provides a shorthand:

SELECT avgIf(number, number > 50) FROM numbers(100)

The same for DISTINCT:

SELECT avg(DISTINCT number)
SELECT avgDistinct(number)

Why filtered aggregates are important? - They allow to implement the "segment comparison" feature in web analytics services.

WITH Region = 'us' AS segment1, Browser = 'Chrome' AS segment2
SELECT uniqIf(UserID, segment1), uniqIf(UserID, segment2)
WHERE segment1 OR segment2

Most of the existing web analytics services are using ClickHouse: CloudFlare Analytics, Microsoft Clarity, Plausible Analytics, PostHog, Splitbee...

https://modern-sql.com/ has some info about more recent additions to SQL to make life easier/write higher level queries.

Once you have the basics of SQL down (like how to write a query), you want to understand what the database actually does with your queries, so you can predict what the query plan "ought to be"/what you want, and you can read the query plans to try to understand why you did or didn't get the plan you expect.

https://use-the-index-luke.com/

And then once you understand the fundamentals, your database's manual is the place to go. Especially different index types (including their internals!) and what kinds of queries they support. The above link goes into depth about b-trees, but there are other types, and you can use b-trees as a reference point to contrast with others.

https://www.postgresql.org/docs/current/index.html

Jennifer Widom’s Databases course is great

https://online.stanford.edu/courses/soe-ydatabases-databases

Otherwise SQL is best learned by solving problems and googling when you’re stuck.

Most developers only need a few SQL constructs like SELECT, WHERE, WITH, JOIN, INSERT, CREATE and GROUP BY. Most devs learn and forget because they don’t use SQL very often. It’s too bad because the relational data structure (or more generally the table) is a super useful abstraction and often much more powerful than lists and dicts.

Data analysts need a few more like OVER (PARTITION BY) and maybe PIVOT/UNPIVOT.

Data engineers of course need many more specifically those related to database object manipulation, and stuff around performance like INDEX.

Really advanced SQL experts do stuff like this.

https://modern-sql.com/

E. F. Codd originally suggested two types of values: "unknown" and "missing". Somehow we got NULL, which represents both.

ANSI SQL:1991 provides an optional feature that introduces a special value UNKNOWN to boolean expressions [1]. But few databases support it. MSSQL [2] is one of the few that do. As I understand it, it's not a data type that be used in tables, but is only a potential result of boolean operations.

[1] https://modern-sql.com/concept/three-valued-logic

[2] https://learn.microsoft.com/en-us/sql/t-sql/language-elements/null-and-unknown-transact-sql

I think Oracle does. My impression is that PostgreSQL sort-of "invented" JSON support in the db, then Oracle (and probably others) added JSON support with a different syntax, and then Oracle got their version defined into the SQL standard. I'm half-guessing here, but it's based on what I've gleaned from Markus Winand's [0] excellent compatibility tables in his slides [1].

[0] https://modern-sql.com/

[1] https://modern-sql.com/slides/ModernSQL-2019-05-08.pdf, for example

(Edited for formatting.)