Using the right data type for some calculation sounds like some obvious advice. There are many blogs about using temporal data types for temporal data, instead of strings. An obvious reason is data integrity and correctness. We don't gain much in storing dates as 2019-09-10 in one record, and as Nov 10, 2019 in the … Continue reading Oracle’s BINARY_DOUBLE Can Be Much Faster Than NUMBER
Using DISTINCT ON in Non-PostgreSQL Databases
A nice little gem in PostgreSQL's SQL syntax is the DISTINCT ON clause, which is as powerful as it is esoteric. In a previous post, we've blogged about some caveats to think of when DISTINCT and ORDER BY are used together. The bigger picture can be seen in our article about the logical order of … Continue reading Using DISTINCT ON in Non-PostgreSQL Databases
Quantified LIKE ANY predicates in jOOQ 3.12
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