2023-09-18 search depth/breadth first
The search clause of with recursive creates a column that allows sorting the result in depth-first or breadth-first order.
The search clause follows immediately after a recursive with element—even before a cycle clause. The primary order is specified by the depth first or breadth first keywords. The following, mandatory by clause takes a list of expression to define the per-level order. Finally, the set clause expects the name of the newly created column, the so-called sequence column.
2023-06-29 Greatest(), least()
The SQL functions greatest and least take one or more arguments✓✗ and return the largest or smallest value if none of the arguments is null.✓✗
SELECT GREATEST(1, 2, 3)
FROM …Greatest and least are scalar functions, not aggregate functions. Unlike min and max they operate on one row at a time.
2023-05-23 Merge
SQL’s merge performs insert, update and delete operations based on when…then rules. It is useful for synchronizing the contents of a table with newer data. Instead of running a delete, and an update and an insert statement, a single merge statement takes care of everything…well, almost everything. Unfortunately, there is a scenario that standard SQL’s merge does not cover, so in the end you might still need two statements. Let’s start from the beginning…
2022-11-15 generated always as (…) (generated columns)
SQL supports generated columns, sometimes called computed columns or virtual columns, whose value is automatically derived from other values of the same table row.
The following example defines a table with two base columns (i.e., non-generated columns): net_price and vat_rate. Further it defines a generated column vat_amount that calculates the VAT amount.
2020-09-10 Blaze-Persistence: Use Modern SQL like native JPA
Guest Post
This is a guest post by Christian Beikov, creator of Blaze-Persistence.
Markus recently published a video in which he talks about Java & SQL, and that lead to a little discussion with me on Twitter. The conclusion Markus often came to is that one has to revert to writing plain SQL for some of the more advanced use cases because of limitations that JPA imposes. I agree that JPA could use an update to support some of the more advanced features of DBMS like set operations, CTEs and recursive CTEs, but that will take some time. Adding such features to a JPA implementation like Hibernate is a first step, but one has to convince a broad audience of people about the benefits. Sometimes people can’t imagine how an ORM could make use of these more advanced DBMS concepts. To avoid having to convince people, I started developing Blaze-Persistence, a query builder API that lives on top of JPA – it implements support for these advanced concepts with the JPA model.
In the following article I will discuss some of the problems and limitations Markus discovered and provide alternate solutions that work with the JPA model by using Blaze-Persistence.
2020-05-04 Java & SQL, Stronger Together
I’ve recently presented my new talk “Java & SQL — Stronger Together” as a free webinar.
The (updated) slides are available as PDF download (10MB), the recording is also available at Vimeo.
2019-11-12 My Interview in “The Art of PostgreSQL”
Back in 2017, Dimitri Fontaine asked me about an interview for his upcoming book. Now there is a second edition, “The Art of PostgreSQL” (affiliate link). We thought take this opportunity to publish this interview here.
2019-10-30 What’s new in Oracle Database Release 19c
The first on-premises download of the Oracle Database 19c became available in April 2019. In the old numbering scheme, this release would just be a patch release, so it is no surprise that this release is “focused on fixing known issues, rather than adding new functionality”.
There are nevertheless some enhancements in the supported SQL dialect and other noteworthy changes.
2019-02-14 What’s new in PostgreSQL 11
PosgreSQL 11 was released four months ago and my review is long overdue. Here we go!
With respect to standard SQL, the main theme in PostgreSQL 11 is window functions (over). For almost eight years, from 2009 until 2017, PostgreSQL was the only major free open-source product to support SQL window functions. Just a year later, by September 2018, all open-source competitors have caught up…and some even overtook PostgreSQL. The PostgreSQL community was prepared. PostgreSQL 11 was just released in 2018, and it has restored and even expanded its leadership position.
This article explains this race and covers other improvements in PostgreSQL 11.
2019-01-15 SQLite in 2018: A state of the art SQL dialect
SQLite is an undervalued database—some people even believe it is a toy database that is not suitable for production use. In fact, SQLite is a very solid database that can handle terabytes of data, but it doesn’t have a network layer.
SQLite is “just” a library, not a server. That, of course, makes it inappropriate for some use cases, and at the same time it makes it the best fit for many other use cases. Really…many other use cases. SQLite even claims to be the most widely deployed and used database engine. I guess that is only possible because SQLite is in the public domain. SQLite is your go-to solution whenever you want to use SQL to store structured data in a file.
The SQL dialect of SQLite is also very strong. For example, it introduced the with clause four years before MySQL did. Lately, it has even added window functions—just five months after MySQL did the same.
This article covers the SQL enhancements that were added to SQLite in 2018, i.e. the new SQL features introduced in versions 3.22.0 through 3.26.0.
2018-11-20 What’s New in Oracle Database 18c
Back in February 2018, Oracle released version 18c of their database for users of the Oracle Cloud and Engineered Systems. It took another five months—until July—before a download became available for on-site installation. After three more months—in October—the free Express Edition (XE) was also upgraded to 18c. I guess it’s safe to say that Oracle Database 18c is finally released. Now it’s time for me to have a look at it from an SQL standards perspective.
Please keep in mind that Oracle Database 18c is just a “minor” release—even though the previous version was 12.2. Why is this? Oracle just decided to use the last two digits of the release year for the version number from now on.
2018-08-22 What’s New in MariaDB 10.3
Let me start with an announcement: From now on, MariaDB is being treated as a distinct database on modern-sql.com.
The reason for the inclusion in my club of major SQL databases is simple: Although MariaDB was originally described as a “branch of MySQL that is, on the user level, compatible with the main version”, both versions have diverged considerably in the past few years. At first, the differences were mostly limited to operative aspects (including some legal aspects). Over the last two years, even the SQL dialects started to diverge notably. Treating MariaDB as a distinct product is the unavoidable consequence for me.
Furthermore, MariaDB’s popularity is still growing and it seems that the MariaDB team is finally embracing the SQL standard. I must actually say that “they now embrace modern SQL standards”—not the SQL-92 standard that’s been overhauled six times.
The release of MariaDB 10.3 demonstrates this in an impressive way. Read on to see what I mean.
2018-06-05 PostgreSQL Standard SQL Gap Analysis
Last week I’ve presented my “PostgreSQL Standard SQL Gap Analysis” at PGCon.org in Ottawa. If this sound familiar you might confuse it with the opposite talk “Features Where PostgreSQL Beats its Competitors” I gave at FOSDEM and PgConf.de this year.
The abstract of the gap analysis:
PostgreSQL supports an impressive number of standard SQL features in an outstanding quality. Yet there remain some cases where other databases exceed PostgreSQL’s capabilities in regard to standard SQL conformance.
This session presents the gaps found during an in-depth comparison of selected standard SQL features among six popular SQL databases. The selected features include, among others, window functions and common tables expressions—both of them were recently introduced to MySQL and MariaDB.
The comparison uses a set of conformance tests I use for my website modern-sql.com. These tests are based on the SQL:2016 standard and attempt to do a rather complete test of the requirements set out in the standard. This includes the correct declared type of expressions as well as the correct SQLSTATE in case of errors (teaser: nobody seems to care about SQLSTATE).
This presentation covers two aspects: (1) features not supported by PostgreSQL but by other databases; (2) features available in PostgreSQL that are less complete or conforming as in other databases.
You can download the slides here [PDF; 5MB].
2018-04-25 One Giant Leap For SQL: MySQL 8.0 Released
“Still using SQL-92?” is the opening question of my “Modern SQL” presentation. When I ask this question, an astonishingly large portion of the audience openly admits to using 25 year old technology. If I ask who is still using Windows 3.1, which was also released in 1992, only a few raise their hand…but they’re joking, of course.
Clearly this comparison is not entirely fair. It nevertheless demonstrates that the know-how surrounding newer SQL standards is pretty lacking. There were actually five updates since SQL-92—many developers have never heard of them. The latest version is SQL:2016.
As a consequence, many developers don’t know that SQL hasn’t been limited to the relational algebra or the relational model since 1999. SQL:1999 introduced operations that don’t exist in relational algebra (with recursive, lateral) and types (arrays!) that break the traditional interpretation of the first normal form.
Since then, so for 19 years, whether or not a SQL feature fits the relational idea isn’t important anymore. What is important is that a feature has well-defined semantics and solves a real problem. The academic approach has given way to a pragmatic one. Today, the SQL standard has a practical solution for almost every data processing problem. Some of them stay within the relational domain, while others do not.
2018-02-05 Standard SQL Features Where PostgreSQL Beats its Competitors
Last Friday I’ve been at the PgDay before FOSDEM in Brussels and presented a selection of “Standard SQL Features Where PostgreSQL Beats its Competitors”. Slides below.
Next time I give this talk (in German and updated) is at the „Deutschsprachige PostgreSQL Konferenz“ in Berlin (registration is open). Find a complete list of my upcoming conference talks at winand.at. There you can also find slides for other talks.
2017-06-15 What’s New in SQL:2016
In December 2016, ISO released a new version of the international SQL standard (ISO/IEC 9075:2016). It supersedes the previous version from 2011.
This article is a brief overview of the new features being introduced into the SQL language. Strictly speaking, this article covers the additions to part 2 of the standard (SQL/Foundation), i.e. the most commonly used part.
This article also shows the availability of these features among six major databases. Note that respective figures—shown below—only reflect whether or not the databases support the features in the way described by the standard. For example, an X in the JSON row does not mean the database has no JSON support—it simply means the database doesn’t support the JSON features described by the SQL standard. As a matter of fact, all tested databases support JSON in some way—but not necessarily in the way described by the standard.
- In the
fromclause - Some functions conform (by “coincidence”)
- Some minor omissions
- No
on overflowclause • Limiteddistinct
