Modern SQL
@ModernSQL
Followers
11K
Following
171
Media
23
Statuses
1K
Still using Windows 3.1? So why stick to SQL-92? A lot has changed in 30 years. We embrace the standard.
Vienna, Austria
Joined February 2015
https://t.co/j7tt9vKtzL now covers DuckDB.
modern-sql.com
SQL has evolved. Beyond the relational model. Discover it now.
1
29
142
GROUP BY ALL Very popular, not yet standard but set to become standard. Also expected for PostgreSQL 19. https://t.co/8oqau6wQJp
modern-sql.com
Automatically put non-aggregate SELECT items into GROUP BY clause
0
2
5
Oracle 26ai¹ was released yesterday. Noteworthy SQL addition: QUALIFY. https://t.co/RoZo63LY47 I bet this starts an avalanche (there are already PostgreSQL patches). ¹ 26ai is the marketing version, 23.26.0 is the technical version.
modern-sql.com
QUALIFY: filter rows based on window functions
3
5
14
After being « gone fishin' » during summertime I just updated my website for the releases that happened during that time: * MariaDB 11.8 and 12.0 * Oracle 23.9 * Db2 12.1.2 All charts on https://t.co/j7tt9vJVKd and https://t.co/LANd9rXpC7 are updated.
use-the-index-luke.com
SQL indexing and tuning tutorial for developers. No unnecessary database details—just what developers need to know. Covers all major SQL databases.
1
7
21
Just updated https://t.co/xcQW5xMJX0 and https://t.co/Bk1fXje2hj for MariaDB 11.7. One enhancement is the FROM-clause column renaming, as described in this article:
modern-sql.com
Rename(alias) columns in the SQL FROM clause
0
2
10
A Christmas present for SQL standard lovers? Part 1 is available for free again. https://t.co/bzVCF4la5w The last edition that was available for free was 2011, AFAIK. And that disappeared when 2016 was released. Merry Christmas :)
1
22
39
The scraping of content for AI training makes some changes required. I'll publish less on my website and RSS feed. The mailing lists will probably stay the same for the time being. If you want to still get everything, subscribe here: https://t.co/sWsaCfYwDw
winand.at
Technical details about SQL and SQL performance as well as strategic insights.
0
2
6
Would you miss Apache Derby when I would remove it from the charts on https://t.co/DbUylcUDcg?
2
1
1
The other direction (creating JSON in SQL) is also possible. This means, JSON in SQL is very useful as an exchange between your client code and the relational model of your database. https://t.co/YNlBEgDKPh ——— ¹ not only
0
0
1
This example uses the power horse of the functions that parse JSON in SQL: JSON_TABLE.
2
0
2
That means SQL can convert a JSON array from the client into a tabular form that can be processed very much like the result of a sub-query. Example: Passing an IN-list via a JSON array WHERE x IN (SELECT y FROM JSON_TABLE(?, '$[*]' COLUMNS (y INT PATH ‘$.y’)) AS jt)
2
0
3
JSON support in SQL is not about storing JSON documents¹. While storing JSON in SQL tables is possible and sometimes quite useful, the true power of JSON in SQL comes from the ability to *process* JSON within SQL.
2
1
6
—this is not only hard but sometimes actually impossible. https://t.co/zwq37gjXU4 * "Index" means "sort-based index (like b-tree)" in this thread.
modern-sql.com
SQL’s EXTRACT provides access to the parts of timestamps and dates (year, month, day, hour, minute, second)
0
0
2
- Inclusive lower bound (>=) - Exclusive upper bound (<) As BETWEEN is inclusive on both ends it cannot be used for this pattern. This pattern avoids the need the pinpoint the last moment of the period of interest...
1
0
4
Further, it preserves the actual chronological order of rows. That means, you could add ORDER BY ts_col “for free” (query doesn’t get slower as the index has the rows in the required order). Just watch out to follow the pattern as shown above...
1
0
2
WHERE ts_col >= DATE'2024-01-01' AND ts_col < DATE'2025-01-01' This just needs a regular index: CREATE INDEX … ON … (ts_col) Why is that a big deal? The index on the column itself is very versatile. It will also be good to find everything of yesterday...
1
0
4
Almost all of the timestamp related function-based indexes are unnecessary. Example: For this WHERE EXTRACT(YEAR FROM ts_col) = 2024 Someone might come up with that index: CREATE INDEX … ON … (EXTRACT(YEAR FROM ts_col)) While it works it is better to re-write the query...
1
1
8
Example: division by zero n / d becomes n / NULLIF(d, 0) If d = 0, NULLIF returns NULL instead of the value of d. NULL just propagates through the division. No exception, just a NULL (unknown) result. NULLIF is very well supported, btw. https://t.co/A0QLaQVJqU
1
2
5
There is no such thing as a division by NULL exception in SQL. Au contraire: Processing NULL values never causes an exception in SQL. That’s why you can use NULL to prevent exceptions cause by bad data: Just map bad values to NULL before the sensitive operation takes place.
1
3
8
While the BMP covers the need of most living languages, it lacks support for symbols that made it into informal language (emojis). Always test your code & engine with non-BPM characters. Ideally one which needs four bytes in the UTF-8 encoding. Here is one for easy c&p: 💩
1
1
6