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.
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.
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…
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.
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.
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.
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.
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.
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.
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.
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.
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.
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].
“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.
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.
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.
from
clauseon overflow
clause • Limited distinct
You can’t catch up on 20 years of SQL evolution in one day. Subscribe the newsletter via E-Mail, Twitter or RSS to gradually catch up and to keep modern-sql.com on your radar.
The essence of SQL tuning in 200 pages
Buy now!
(paperback and/or PDF)
Paperback also available at Amazon.com.
Markus offers SQL training and consulting for developers working at companies of all sizes.
Learn more »