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.
modern-sql.com/caniuse/nullif
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.
modern-sql.com/caniuse/nullif
1
2
5
SQL's data types CHAR and VARCHAR are about characters. Sounds like a no-brainer, doesn't it?
Well, a consequence is that the number in the parenthesis refer to characters, not bytes.
Unfortunately, some SQL engines don't get that right—causing major confusion among users.
Well, a consequence is that the number in the parenthesis refer to characters, not bytes.
Unfortunately, some SQL engines don't get that right—causing major confusion among users.
1
8
13
SQL's FILTER clause should be easy to implement for every engine. Why is it still not supported widely?
Sure it can be emulated easily—and ugly. The value on readability (and learning) is huge.
Please vendors: Just get it done.
modern-sql.com/feature/filter
Sure it can be emulated easily—and ugly. The value on readability (and learning) is huge.
Please vendors: Just get it done.
modern-sql.com/feature/filter
1
8
32
Configuration recommendations to secure DBMSes:
Db2: cisecurity.org/benchmark/ibm_…
MariaDB: cisecurity.org/benchmark/mari…
MySQL: cisecurity.org/benchmark/orac…
SQL Server: cisecurity.org/benchmark/micr…
Oracle DB: cisecurity.org/benchmark/orac…
PostgreSQL: cisecurity.org/benchmark/post…
Db2: cisecurity.org/benchmark/ibm_…
MariaDB: cisecurity.org/benchmark/mari…
MySQL: cisecurity.org/benchmark/orac…
SQL Server: cisecurity.org/benchmark/micr…
Oracle DB: cisecurity.org/benchmark/orac…
PostgreSQL: cisecurity.org/benchmark/post…
0
9
26
SQL statements can become large-ish.
For maintenance, they should be broken down into smaller units one can easily understanding.
The WITH clause is an important tool to write literate SQL.
modern-sql.com/use-case/liter…
For maintenance, they should be broken down into smaller units one can easily understanding.
The WITH clause is an important tool to write literate SQL.
modern-sql.com/use-case/liter…
0
5
27
If you like Wordle and SQL, then you'll love SQuizL!
devgym.oracle.com/pls/apex/f?p=1…
Show off your SQL-Syntax foo and learn something new every day.
By Oracle, thus sometimes proprietary Oracle DB syntax.
devgym.oracle.com/pls/apex/f?p=1…
Show off your SQL-Syntax foo and learn something new every day.
By Oracle, thus sometimes proprietary Oracle DB syntax.
0
6
34
@richsoule For the second example I find the following more beautiful. Besides the solvable compatibility issues the main problem of that is performance due to the inability to use indexes on actors.name for every product I know.
Having that said I still find it more beautiful.
Having that said I still find it more beautiful.
2
0
5
The OVER clause supports three frame units: ROWS, RANGE and GROUPS.
RANGE uses the values of the ORDER BY column to determine which rows fall into the window frame.
modern-sql.com/caniuse/over_r…
RANGE uses the values of the ORDER BY column to determine which rows fall into the window frame.
modern-sql.com/caniuse/over_r…
1
8
46
The WITH clause allows you to write more literate SQL.
Meanwhile supported by most SQL dialects.
modern-sql.com/use-case/liter…
Meanwhile supported by most SQL dialects.
modern-sql.com/use-case/liter…
0
11
46
@knitmaths You might confuse that with the general availability of TRIM in SQL server. The option to remove other characters than space, using the FROM clause, was added in SQL Server 2022.
learn.microsoft.com/en-us/sql/t-sq…
learn.microsoft.com/en-us/sql/t-sq…
0
0
0
What about the next SQL standard?
Peter Eisentraut (@petereisentraut) gave a high-level overview in September.
youtu.be/kNPvO6MexzU?li…
(If you are interested in ISO processes, start the video from the beginning).
Peter Eisentraut (@petereisentraut) gave a high-level overview in September.
youtu.be/kNPvO6MexzU?li…
(If you are interested in ISO processes, start the video from the beginning).
0
2
12
The picture in the twitter card is not current (current picture below).
I updated the page yesterday but Twitter STILL shows the old picture!!!!11!!
@elonmusk Fix IT!
Oh damn...it might be his fault...
I updated the page yesterday but Twitter STILL shows the old picture!!!!11!!
@elonmusk Fix IT!
Oh damn...it might be his fault...
1
0
2
UNION, EXCEPT and INTERSECT — the three set operators defined in the SQL standard — since SQL-92.
October 2022: One of the most popular SQL engines finally learned about INTERSECT & EXCEPT.
MySQL in its 8.0.31 release.
Pictured: EXCEPT DISTINCT
modern-sql.com/caniuse/except
October 2022: One of the most popular SQL engines finally learned about INTERSECT & EXCEPT.
MySQL in its 8.0.31 release.
Pictured: EXCEPT DISTINCT
modern-sql.com/caniuse/except
1
4
25
More Than a Query Language: SQL in the 21st Century by Markus Winand
youtube.com/watch?v=MnEDHF…
youtube.com/watch?v=MnEDHF…
0
3
13
Given a trips table (customer_id, start_from, end_at, start_at_time, end_at_time), how would you find the list of all the customers who started yesterday from point A and ended yesterday at point P?
Can you do without a self-join?
reddit.com/r/SQL/comments…
Can you do without a self-join?
reddit.com/r/SQL/comments…
0
3
23
Deep Neural Network implemented in pure SQL over BigQuery
towardsdatascience.com/deep-neural-ne…
Note that BigQuery doesn't support WITH RECURSIVE, which could be used instead of the ten-fold nesting.
towardsdatascience.com/deep-neural-ne…
Note that BigQuery doesn't support WITH RECURSIVE, which could be used instead of the ten-fold nesting.
0
5
12
Did you know you can click a feature name in a support matrix to see when each DB started supporting this feature?
modern-sql.com/feature/with#c…
modern-sql.com/feature/with#c…
0
28
38
