Modern SQL

A couple of sites worth checking out to level up, both by Markus Winand: https://modern-sql.com/ https://use-the-index-luke.com/

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/

Markus's site https://modern-sql.com/ is fantastic, I've learned a ton of useful tricks from it.

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

Since you ased about indexing in particular, here is my site about indexing: https://use-the-index-luke.com/ You might also want to follow my other project: https://modern-sql.com/

Markus is modest; he is the author of the top post, and imho is up with Joe Celko in offering great techniques to use SQL effectively (both at http://modern-sql.com/ as above and http://use-the-index-luke.com/). I'm a fan.

The list is missing http://use-the-index-luke.com and http://modern-sql.com which are some of the very best resources out there for SQL in general. It also misses https://www.pgexercises.com which is an amazing resource for learning the specifics of PostgreSQL in particular.

i would also recommend the wonderful https://modern-sql.com/

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

Please elaborate because that sentence make barely no sense for modern SQL engine such as PostgreSQL. A good ressource to start exploring would be this site with compare features between vendors: https://modern-sql.com/

And even so, Postgres is closer to the spec than Oracle is. (Based on perusing https://modern-sql.com)

https://modern-sql.com/ is another site with compatibility graphs.

Markus Winand is doing something like that at https://modern-sql.com/. But it's not nearly as extensive as caniuse.com

Markus Winand [0]. He is the creator behind "Use the index, Luke!" [1] and "Modern SQL" [2]. [0]: https://twitter.com/MarkusWinand [1]: https://use-the-index-luke.com/ [2]: https://modern-sql.com/

https://www.sqlite.org/queryplanner.html https://use-the-index-luke.com/ https://modern-sql.com/

> Is there a caniuse.com for SQL? Not exactly the same, but: https://modern-sql.com/

According to [1], ISO/IEC 9075:2016 specifies JSON functionality in ISO SQL, though I don't have access to it/didn't buy the standard text. I also don't know whether Postgres etc. is designed to follow the standard, as Postgres' implementation predates it, but would expect sqlite to. [1]: https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016

@MarkusWindand: Thank you for the Websites [1],[2],[3] and the book »SQL Performance Explained«[4]. [1] https://modern-sql.com/ [2] https://use-the-index-luke.com/ [3] https://db-engines.com/ [4] https://www.amazon.de/SQL-Performance-Explained-Everything-performance…

Really good introduction to some of the more "modern" features: https://modern-sql.com/

This have help me in discover extra powers of sql: http://modern-sql.com/ And http://use-the-index-luke.com/

Possibly relevant: Modern SQL is a good resource, by Markus Winand, on the newer aspects of the language. http://modern-sql.com/

He's started work on his next book[0] which looks to be interesting. [0] http://modern-sql.com/

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

You raise a good point. Skimming the original article, I didn't really understand why the author didn't discuss "WITH" CTEs (for SQL newbies, common table expressions, see https://modern-sql.com/feature/with ) as alternative composition mechanisms. Or even SQL views. But your ergonomics comment makes sense to me.

Another thing you are apparently missing is deterministic ordering for tied elements: https://modern-sql.com/blog/2026-03/order-equivalence-over-clause

`CUME_DIST() OVER (ORDER BY column)` https://modern-sql.com/caniuse/cume_dist

ANSI SQL has had IS [NOT] UNKNOWN since SQL:1999 [1]. It's an optional feature that some databases don't support. Postgres, MySQL, and MSSQL do support it. [1] https://modern-sql.com/concept/three-valued-logic#compatibility

There's another good, technical write-up on NULL behavior in SQL at modern-sql.com https://modern-sql.com/concept/null (Note: I am not affiliated with that bloh/website in any way, shape, or form.)

Surprised that this doesn't mention "IS DISTINCT FROM" ( https://modern-sql.com/caniuse/is-distinct-from ) (Although in rare cases that is even weirder: https://stackoverflow.com/a/58998043 )

The good thing here is that other databases like MySQL, TiDB, SQLite etc also support this. so this. And even recursive CTEs are well supported. https://modern-sql.com/caniuse/with_(non-recursive,_top_level)