Oracle Database 19c came out in 2019, and plenty of companies will run it for years yet (it's the long-term support release). Oracle AI Database 26ai is the current one. In between came 21c and 23ai, and everything they added to SQL is in 26ai. This part covers the SQL you can write on 26ai and can't write on 19c, and shows the 19c way next to every one of them, because you'll probably need both.
Think of it as the difference between a Test match and a T20: same game, same rules mostly, but some shots only exist in the newer format.
The list
Every row was run on both versions (19c: 19.33; 26ai: 23.26.2 on apex.oracle.com).
| Feature | 19c | 26ai | Page |
|---|---|---|---|
SELECT without FROM (goodbye, DUAL) |
no | yes | 12.1 |
BOOLEAN: TRUE, FALSE, boolean columns and expressions |
no | yes | 12.1 |
GROUP BY a column alias, or a position (GROUP BY 1) |
no | yes | 12.2 |
QUALIFY: filter on a window function without a subquery |
no | yes | 12.2 |
Table value constructor: FROM (VALUES (...), (...)) |
no | yes | 12.3 |
Multi-row INSERT ... VALUES (...), (...) |
no | yes | 12.3 |
WINDOW clause: name a window once, use it many times |
no | yes (since 21c) | 12.4 |
GROUPS frames and EXCLUDE in window functions |
no | yes (since 21c) | 12.4 |
MINUS ALL, INTERSECT ALL |
no | yes (since 21c) | 12.5 |
UPDATE ... FROM and DELETE ... FROM (joins in DML) |
no | yes | 12.6 |
RETURNING OLD and RETURNING NEW |
no | yes | 12.6 |
CREATE ... IF NOT EXISTS, DROP ... IF EXISTS |
19.28 and later | yes | 12.7 |
| Annotations on tables and columns | no | yes | 12.7 |
TIME_BUCKET, CEIL/FLOOR on dates, SUM/AVG of intervals |
no | yes | 12.8 |
VECTOR and VECTOR_DISTANCE |
no | yes | 12.9 |
Two surprises from testing:
IF [NOT] EXISTSworks on 19c too, from release update 19.28. Oracle back-ported it. On an older 19c you still need the PL/SQL workaround in 12.7.ANY_VALUE(any one value from a group, when you don't care which) is often listed as new, but it's in 19c release updates as well, so it's covered in Part 3 as a 19c+ feature.
Not here yet, because apex.oracle.com doesn't let a workspace create them (ORA-01031: insufficient privileges): SQL domains (CREATE DOMAIN) and property graphs
(CREATE PROPERTY GRAPH, GRAPH_TABLE). They'll get pages once they're tested on a 26ai
database where you have those privileges (the free container image, or an Autonomous Database).
How the badges work
- 19c+: works on 19c and everything newer, including 26ai.
- 26ai only: needs 26ai (or 21c/23ai for the "since 21c" rows). Every 26ai-only query in this part has a 19c version on the same page that returns exactly the same rows.
The tests use that: on 19c the harness skips the 26ai-only ways and runs the 19c ones; on 26ai it runs both and checks they agree.
Which version am I on?
On 26ai the version number starts with 23 (for example 23.26.2.0.0), because 26ai is the release name of the version 23 code line.
select version_full from product_component_version;