I've blogged about generic ways of getting top 1 or top n per category queries before on this blog. An Oracle specific version in that post used the arcane KEEP syntax: SELECT max(actor_id) KEEP ...
https://blog.jooq.org/getting-top-1-values-per-group-in-oracle/
In a previous blog post, we've advertised the use of SQL EXISTS rather than COUNT(*) to check for existence of a value in SQL. I.e. to check if in the Sakila database, actors called WAHLBERG have...
https://blog.jooq.org/an-efficient-way-to-check-for-existence-of-multiple-values-in-sql/
One of jOOQ's key features so far has always been to render pretty much exactly the SQL that users expect, without any surprises - unless some emulation is required to make a query work, of cours...
https://blog.jooq.org/a-hidden-benefit-of-implicit-joins-join-elimination/
jOOQ 3.19 finally delivers on a set of features that will greatly simplify your queries further, after jOOQ 3.11 introduced implicit to-one joins: Explicit path joins To-many path joins Implicit ...
https://blog.jooq.org/jooq-3-19s-new-explicit-and-implicit-to-many-path-joins/
In MySQL, you cannot do this: create table t (i int primary key, j int); insert into t values (1, 1); update t set j = (select max(j) from t) + 1; The UPDATE statement will raise an error as foll...
Do you need to add a JDBC driver to your application, and don't know its Maven coordinates? This blog post lists the most popular drivers from the jOOQ integration tests. Look up the latest versi...
https://blog.jooq.org/maven-coordinates-of-the-most-popular-jdbc-drivers/
Occasionally, you want to write a SQL query and fetch a hierarchy of data, whose flat representation may look like this: SELECT id, parent_id, label FROM t_directory; The result might be: |id |pa...
https://blog.jooq.org/how-to-turn-a-list-of-flat-elements-into-a-hierarchy-in-java-sql-or-jooq/
One of the more frequent questions about jOOQ is how to write a derived table (or a CTE). The jOOQ manual shows a simple example of a derived table: In SQL: SELECT nested.* FROM ( SELECT AUTHOR_I...
I've found an interesting question on Twitter, recently. Is there any performance impact of using FILTER in SQL (PostgreSQL, specifically), or is it just syntax sugar for a CASE expression in an ...
https://blog.jooq.org/the-performance-impact-of-sqls-filter-clause/
One of MySQL 8's biggest improvements is the support of window functions. As I always said in conferences, there's SQL before window functions and SQL after window functions. Once you start using...
https://blog.jooq.org/emulating-window-functions-in-mysql-5-7/
A frequently encountered doubt people have when using jOOQ is to decide when a "complex" query should be written using jOOQ API vs. when it should be implemented using native SQL. The jOOQ manual...
https://blog.jooq.org/when-to-use-jooq-and-when-to-use-native-sql/
The standard SQL WITH clause has been tremendously helpful in structuring SQL queries. Instead of nesting everything in unreadable derived tables like this: SELECT actor_id, name, COUNT(*) FROM (...
https://blog.jooq.org/lateral-is-your-friend-to-create-local-column-variables-in-sql/
No need for expensive Tableau subscriptions. Ditch Microsoft Excel. Just use native PostgreSQL to quickly visualise your data! Here's an idea I had for a while. As you may know, jOOQ can produce ...
https://blog.jooq.org/how-to-plot-an-ascii-bar-chart-with-sql/
Starting with jOOQ 3.17, the Condition type extends the Field
The H2 database is an immensely popular in-memory database product mostly used by Java developers for testing. If you check out the DB-Engines ranking, it ranks 50th, which is quite impressive, a...
One of jOOQ 3.17's coolest new features are client side computed columns. jOOQ 3.16 already added support for server side computed columns, which many of you appreciate for various reasons. What'...
https://blog.jooq.org/create-dynamic-views-with-jooq-3-17s-new-virtual-client-side-computed-columns/
I stumbled upon a very interesting question on Stack Overflow about how to use jOOQ's MULTISET operator to nest a collection, and then filter the result by whether that nested collection contains...
https://blog.jooq.org/how-to-filter-a-sql-nested-collection-by-a-value/
It's been a while since jOOQ 3.15 has been released with its revolutionary standard SQL MULTISET emulation feature. A thing that has been long overdue and which I promised on twitter a few times ...
https://blog.jooq.org/the-performance-of-various-to-many-nesting-algorithms/
Every now and then, I see folks lament the SQL syntax's peculiar disconnect between the lexical order of operations (SELECT .. FROM) the logical order of operations (FROM .. SELECT) Most recently...
https://blog.jooq.org/changing-select-from-into-from-select-does-not-fix-sql/
The jOOQ API is all about convenience, and as such, an important
operation (the most important one?) like fetch() must come with
convenience, too. The default way to fetch data is this:
Result https://blog.jooq.org/the-many-different-ways-to-fetch-data-in-jooq/
An interesting hint by Vladimir Sitnikov has made me think about a new benchmark for jOOQ: https://twitter.com/lukaseder/status/1407662449331949568 The benchmark should check whether single row q...
https://blog.jooq.org/setting-the-jdbc-statement-setfetchsize-to-1-for-single-row-queries/
A really cool, recent question on Stack Overflow was about how to map a nested collection into a Java Map with jOOQ. In the past, I've blogged about the powerful MULTISET operator many times, whi...
Since jOOQ 3.4, we have an API that simplifies transactional logic on top of JDBC in jOOQ, and starting from jOOQ 3.17 and #13502, an equivalent API will also be made available on top of R2DBC, f...
A lot of RDBMS support standard SQL sequences of some form. The standard SQL syntax to create a sequence is: CREATE SEQUENCE s; The following is how you could fetch a value from this sequence, us...
https://blog.jooq.org/how-to-fetch-sequence-values-with-jooq/
For SQL beginners, there's a bit of an esoteric syntax named PARTITION BY, which appears all over the place in SQL. It always has a similar meaning, though in quite different contexts. The meanin...
https://blog.jooq.org/various-meanings-of-sqls-partition-by-syntax/
Questions that might be a bit more difficult to solve using ordinary SQL are questions of the kind: What films have the same actors as a given film X? As always, we're using the sakila database f...
https://blog.jooq.org/use-multiset-predicates-to-compare-data-sets/
If you're running on PostgreSQL, you could try the following cool query: WITH RECURSIVE r (r, i) AS ( SELECT random(), i FROM generate_series(1, 1000000) AS t (i) ), s (ri, s, i) AS ( SELECT i, r...
I recently stumbled upon a standard SQL feature that was implemented, to my surprise, in HSQLDB. The keyword is CORRESPONDING, and it can be used with all set operations, including UNION, INTERSE...
https://blog.jooq.org/a-rarely-seen-but-useful-sql-feature-corresponding/
N+1 queries are a popular problem in many applications that run SQL queries. The problem can be described easily as follows: 1 query fetching a parent value is runN queries fetching each individu...
https://blog.jooq.org/using-jooqs-diagnosticsconnection-to-detect-n1-queries/
One of the coolest things about using and making jOOQ is that we get to discover the best extensions to the standard SQL language by vendors, and add support for those clauses in jOOQ via emulati...
I'm answering many jOOQ questions on Stack Overflow, and a lot of times. The problem has the same cause: People not using jOOQ's code generator. The main reason people seem not to be using it, is...
https://blog.jooq.org/why-you-should-use-jooq-with-code-generation/
The upcoming jOOQ 3.16 will finally offer support for the various RDBMS GIS extensions via issue #982. This is great news per se, and will be covered in a future blog post, when the integration i...
https://blog.jooq.org/fun-with-postgis-mandelbrot-set-game-of-life-and-more/
I've recently discovered a pleasant new addition to PostgreSQL 14, the new enable_memoize flag that improves the performance of some nested loop joins where statistics hint at this being appropri...
https://blog.jooq.org/postgresql-14s-enable_memoize-for-improved-performance-of-nested-loop-joins/
The SQL standard knows an interesting feature where you can project any functional dependencies of a primary (or unique) key that is listed in the GROUP BY clause without having to add that funct...
https://blog.jooq.org/functional-dependencies-in-sql-group-by/
jOOQ's parser can't parse every possible SQL syntax. Try this random PostgreSQL syntax: ALTER SYSTEM RESET ALL And the jOOQ parser will complain: DOMAIN, INDEX, SCHEMA, SEQUENCE, SESSION, TABLE, ...
https://blog.jooq.org/the-jooq-parser-ignore-comment-syntax/
One of the strengths of modern RDBMS is the capability to mix the powerful SQL language with procedural code. SQL is a 4th generation programming language (4GL), and as such, extremely well suite...
https://blog.jooq.org/vendor-agnostic-dynamic-procedural-logic-with-jooq/
A very little known feature in jOOQ is the Formattable.formatChart() capability, which allows for formatting any jOOQ result as an ASCII chart. This can be useful for quick plotting of results in...
It's been almost 1 year now since jOOQ 3.14 was released in October 19, 2020 with SQL/JSON (and SQL/XML) support. Half a year later, we've released jOOQ 3.15 with MULTISET support, which builds o...
One of the biggest new features of the recently released jOOQ 3.15 is its new support for reactive querying via R2DBC. This has been a highly popular feature request, and we finally delivered on ...
https://blog.jooq.org/reactive-sql-with-jooq-3-15-and-r2dbc/
This is how SQL should have been used all along. They called it The Third Manifesto, ORDBMS, or other things. Regrettably, it never really took off. Because most vendors didn't adopt it. And thos...
https://blog.jooq.org/jooq-3-15s-new-multiset-operator-will-change-how-you-think-about-sql/
Do you need to know what RDBMS Server version you're on, and you only have SQL at your disposal? No problem. Most RDBMS provide you with that information in some form of meta data table. Here's h...
https://blog.jooq.org/how-to-get-an-rdbms-server-version-with-sql/
A problem few developers are aware of is the possibility of running into "cursor cache contention" or "execution plan cache contention" problems when using IN lists in SQL. The problem that is de...
When paginating results in SQL, we use standard SQL OFFSET .. FETCH or a vendor specific version of it, such as LIMIT .. OFFSET. For example: SELECT first_name, last_name FROM actor ORDER BY acto...
https://blog.jooq.org/calculating-pagination-metadata-without-extra-roundtrips-in-sql/
In the past years, we've invested a lot of effort into improving our procedural language capabilities in jOOQ. What started with a simple internal API to support the emulations of DDL clauses lik...
https://blog.jooq.org/translating-stored-procedures-between-dialects/
So, @rotnroll666 nerd sniped me again. Apparently, the Neo4j Cypher query language supports arbitrary reductions, just like any functional collection API, oh say, the JDK Stream API: Stream.of(2,...
https://blog.jooq.org/implementing-a-generic-reduce-aggregate-function-with-sql/
Over the past 13 years, jOOQ has accrued quite some internal features, which you, the user, are not exposed to. One very interesting feature is the capability for any arbitrary jOOQ expression tr...
https://blog.jooq.org/jooq-internals-pushing-up-sql-fragments/
While jOOQ is mostly being used as an internal SQL DSL for embedded, dynamic SQL in Java, where it offers the best solution on the market, jOOQ is increasingly also used for one of its secondary ...
https://blog.jooq.org/automatically-transform-oracle-style-implicit-joins-to-ansi-join-using-jooq/
One of the main features of ORMs is M as in Mapping. Libraries like jOOQ help auto-mapping flat or nested database records onto Java classes that have the same structure as the SQL result set. Th...
https://blog.jooq.org/nesting-collections-with-jooq-3-14s-sql-xml-or-sql-json-support/
I was asked a very interesting question on Twitter just now: https://twitter.com/connolly_s/status/1303957373107818497 Can we have "constant" foreign key columns in (PostgreSQL) tables? Luckily, ...
https://blog.jooq.org/having-constant-columns-in-foreign-keys/
There are a few ways to compare two similar tables in SQL. Assuming PostgreSQL syntax, we might have this schema: CREATE TABLE t1 (a INT, b INT, c INT); CREATE TABLE t2 (a INT, b INT, c INT); INS...
https://blog.jooq.org/use-natural-full-join-to-compare-two-tables-in-sql/