Quantified comparison predicates One of SQL's weirdes features are quantified comparison predicates. I've hardly ever seen these in the wild: SELECT * FROM t WHERE id = ANY (1, 2, 3) The above example is equivalent to using the much more readable IN predicate: SELECT * FROM t WHERE id IN (1, 2, 3) This … Continue reading Quantified LIKE ANY predicates in jOOQ 3.12
jOOQ 3.12 Released With a new Procedural Language API
jOOQ 3.12 has been released with a new procedural language API, new data types, MemSQL support, formal Java 11+ support, a much better parser, and reactive stream API support In this release, we've focused on a lot of minor infrastructure tasks, greatly improving the overall quality of jOOQ. We've reworked some of our automated integration … Continue reading jOOQ 3.12 Released With a new Procedural Language API
How to Fetch All Current Identity Values in Oracle
Oracle 12c has introduced the useful SQL standard IDENTITY feature, which is essentially just syntax sugar for binding a sequence to a column default. We can use it like this: create table t1 (col1 number generated always as identity); create table t2 (col2 number generated always as identity); insert into t1 values (default); insert into … Continue reading How to Fetch All Current Identity Values in Oracle
How to Use jOOQ’s Commercial Distributions with Spring Boot
Spring Boot is great to get started very quickly with what the Spring Boot authors have evaluated to be useful defaults. This can be a lot of help when you're doing things for the first time, and have no way to copy paste working Maven pom.xml files from existing projects, for example. When working with … Continue reading How to Use jOOQ’s Commercial Distributions with Spring Boot
How to Write a Simple, yet Extensible API
How to write a simple API is already an art on its own. I didn't have time to write a short letter, so I wrote a long one instead. ― Mark Twain But keeping an API simple for beginners and most users, and making it extensible for power users seems even more of a challenge. … Continue reading How to Write a Simple, yet Extensible API
Using IGNORE NULLS With SQL Window Functions to Fill Gaps
I found a very interesting SQL question on Twitter recently: https://twitter.com/vikkiarul/status/1120669222672261120 Rephrasing the question: We have a set of sparse data points: +------------+-------+ | VALUE_DATE | VALUE | +------------+-------+ | 2019-01-01 | 100 | | 2019-01-02 | 120 | | 2019-01-05 | 125 | | 2019-01-06 | 128 | | 2019-01-10 | 130 | +------------+-------+ … Continue reading Using IGNORE NULLS With SQL Window Functions to Fill Gaps
Calling an Oracle Function with PL/SQL BOOLEAN Type from SQL
One of the most wanted features in the Oracle database is the BOOLEAN type. The SQL standard specified it a while ago, and RDBMS like PostgreSQL show how powerful it can be, e.g. when using the EVERY() aggregate function. Before you move on reading this article, note that starting with Oracle 23c, the standard BOOLEAN … Continue reading Calling an Oracle Function with PL/SQL BOOLEAN Type from SQL
The Difference Between SQL’s JOIN .. ON Clause and the Where Clause
A question that is frequently occurring among my SQL training's participants is: What's the difference between putting a predicate in the JOIN .. ON clause and the WHERE clause? I can definitely see how that's confusing some people, as there seems to be no difference at first sight, when running queries like these, e.g. in … Continue reading The Difference Between SQL’s JOIN .. ON Clause and the Where Clause
The Cost of Useless Surrogate Keys in Relationship Tables
What's a good natural key? This is a very difficult question for most entities when you design your schema. In some rare cases, there seems to be an "obvious" candidate, such as a variety of ISO standards, including: ISO 639 language codesISO 3166 country codesISO 4217 currency codes But even in those cases, there might … Continue reading The Cost of Useless Surrogate Keys in Relationship Tables
Calculating Weighted Averages When Joining Tables in SQL
I stumbled upon a very interesting jOOQ question on Stack Overflow that required the calculation of a weighted average. Why is that. Problem description Assuming you have this database (using PostgreSQL syntax): create table transactions ( id bigint not null primary key, lines bigint not null, price numeric(18, 2) not null, profit numeric(18, 2) not … Continue reading Calculating Weighted Averages When Joining Tables in SQL