Oracle is one of the few databases that implements the SQL standard ORDBMS extensions, which essentially allow for nested collections. Other databases that have these features to some extent are CUBRID, Informix, PostgreSQL. Oracle has two types of nested collections: -- Nested tables CREATE TYPE t1 AS TABLE OF VARCHAR2(10); / -- Varrays CREATE TYPE … Continue reading How to Write Multiset Conditions With Oracle VARRAY Types
How SQL DISTINCT and ORDER BY are Related
One of the things that confuse SQL users all the time is how DISTINCT and ORDER BY are related in a SQL query. The Basics Running some queries against the Sakila database, most people quickly understand: SELECT DISTINCT length FROM film This returns results in an arbitrary order, because the database can (and might apply … Continue reading How SQL DISTINCT and ORDER BY are Related
PostgreSQL 11’s Support for SQL Standard GROUPS and EXCLUDE Window Function Clauses
Exciting discovery when playing around with PostgreSQL 11! New SQL standard window function clauses have been supported. If you want to play with this, you can do so very easily using docker: docker pull postgres:11 docker run --name POSTGRES11 -e POSTGRES_PASSWORD=postgres -d postgres:11 docker run -it --rm --link POSTGRES11:postgres postgres psql -h postgres -U postgres … Continue reading PostgreSQL 11’s Support for SQL Standard GROUPS and EXCLUDE Window Function Clauses
Using UNPIVOT to Traverse a Configuration Table’s Rows and Columns
Imagine you have a configuration table like the following: CREATE TABLE rule ( name VARCHAR2(50) NOT NULL PRIMARY KEY, enabled NUMBER(1) DEFAULT 1 NOT NULL CHECK (enabled IN (0,1)), priority NUMBER(10) DEFAULT 0 NOT NULL, flag1 NUMBER(3) DEFAULT 0 NOT NULL, flag2 NUMBER(3) DEFAULT 0 NOT NULL, flag3 NUMBER(3) DEFAULT 0 NOT NULL, flag4 NUMBER(3) … Continue reading Using UNPIVOT to Traverse a Configuration Table’s Rows and Columns
Oracle’s OFFSET .. FETCH Can be Slower than Classic ROWNUM Filtering
One of Oracle 12c's coolest features was the introduction of the SQL standard OFFSET .. FETCH clause, as we can now write things like: SELECT * FROM film ORDER BY film_id FETCH FIRST 1 ROW ONLY This is querying the Sakila database. Most other databases had this clause (or a non-standard version of it) for … Continue reading Oracle’s OFFSET .. FETCH Can be Slower than Classic ROWNUM Filtering
How to Patch Your IDE to Fix an Urgent Bug
Clock's ticking. JDK 11 will remove a bunch of deprecated modules through JEP 320, which includes the Java EE modules, which again includes JAXB, a dependency of many libraries, including jOOQ. Thus far, few people have upgraded to Java 9 or 10, as these aren't LTS releases. Unlike in the old days, however, people will … Continue reading How to Patch Your IDE to Fix an Urgent Bug
jOOQ 3.11 Released With 4 New Databases, Implicit Joins, Diagnostics, and Much More
Today, jOOQ 3.11 has been released with support for 4 new databases, implicit joins, diagnostics, and much more New Databases Supported At last, 4 new SQL dialects have been added to jOOQ! These are: jOOQ Professional Edition Aurora MySQL Edition Aurora PostgreSQL Edition Azure SQL Data Warehouse jOOQ Enterprise Edition Teradata Implicit Joins One of … Continue reading jOOQ 3.11 Released With 4 New Databases, Implicit Joins, Diagnostics, and Much More
Truth First, or Why You Should Mostly Implement Database First Designs
In this much overdue article, I will explain why I think that in almost all cases, you should implement a "database first" design in your application's data models, rather than a "Java first" design (or whatever your client language is), the latter approach leading to a long road of pain and suffering, once your project … Continue reading Truth First, or Why You Should Mostly Implement Database First Designs
How to Group By “Nothing” in SQL
The SQL standard knows a lesser known feature called GROUPING SETS. One particular side-effect of that feature is that we can group by "nothing" in SQL. E.g. when querying the Sakila database: SELECT count(*) FROM film GROUP BY () This will yield: count | ------| 1000 | What's the point, you're asking? Can't we just … Continue reading How to Group By “Nothing” in SQL
Selecting all Columns Except One in PostgreSQL
Google's BigQuery has a very interesting SQL language feature, which I've missed many times in other databases: select: SELECT [{ ALL | DISTINCT }] { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ] [ REPLACE ( expression [ AS ] column_name [, ...] ) ] | expression [ [ AS ] alias … Continue reading Selecting all Columns Except One in PostgreSQL