Mastering ANTI JOIN in SQL with jOOQ: A Complete Guide
Java, SQL and jOOQ.by lukaseder7 May 2026
ANTI JOIN is a very useful operator from relational algebra. Regrettably, only few dialects support it in terms of SQL syntax, as we’ve written earlier.
Java, SQL and jOOQ.
blog.jooq.orgadded August 2026
Avoiding SQL Ambiguities caused by JOIN USING and NATURAL JOIN
Java, SQL and jOOQ.by lukaseder4 May 2026
Some SQL operators are as esoteric as they’re powerful. One of the oldest operator that you’ve likely hardly ever used in real world applications is NATURAL JOIN which is the default in relational...
top 5%Enhancing jOOQ Debug Logging for Safer Production Use
Java, SQL and jOOQ.by lukaseder27 Mar 2026
One of jOOQ’s most popular feature is the out-of-the-box debug logging experience. jOOQ developers find this feature very useful when developing their applications.
Consider using JSON arrays instead of objects to transfer data
Java, SQL and jOOQ.by lukaseder11 Aug 2025
When implementing the awesome MULTISET operator in jOOQ, its implementation mostly relied on SQL/JSON support of various RDBMS.
A few modern SQL dialects have started introducing lambda expressions
Java, SQL and jOOQ.by lukaseder27 Mar 2025
ARRAY types are a part of the ISO/IEC 9075 SQL standard. The standard specifies how to: - Construct arrays - Nest data into arrays (e.g.
A SQL MERGE statement performs actions based on a RIGHT JOIN
Java, SQL and jOOQ.by lukaseder13 Mar 2025
RIGHT JOIN is an esoteric feature in the SQL language, and hardly ever seen in the real world, because almost every RIGHT JOIN can just be expressed as an equivalent LEFT JOIN.
Why I don't document quirks of a feature on the feature itself.
Java, SQL and jOOQ.by lukaseder28 Feb 2025
Every product manager knows this situation: - A user works with feature X1 . - They find a limitation / bug / quirk and want to work around it.
How to implement FILTER semantics with Oracle JSON aggregate functions
Java, SQL and jOOQ.by lukaseder3 Jun 2024
A cool standard SQL:2003 feature is the aggregate FILTER clause, which is supported natively by at least these RDBMS: - ClickHouse - CockroachDB - DuckDB - Firebird - H2 - HSQLDB - PostgreSQL -...
Oracle's way to get multiple values in a top 1 per group query
Java, SQL and jOOQ.by lukaseder1 Mar 2024
I’ve blogged about generic ways of getting top 1 or top n per category queries before on this blog.
What's faster, COUNT(*) or COUNT(*) with LIMIT in SQL? Let's check
Java, SQL and jOOQ.by lukaseder16 Feb 2024
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.
Implicit path joins may now skip unnecessary tables in the join tree
Java, SQL and jOOQ.by lukaseder10 Jan 2024
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 course.
jOOQ 3.19 offers many new and useful path based join features
Java, SQL and jOOQ.by lukaseder28 Dec 2023
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: What are these features? Many ORMs (e.g.
jOOQ workaround for "can't specify target table for update in FROM clause"
Java, SQL and jOOQ.by lukaseder20 Dec 2023
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...
jOOQ 3.19 has been released with support for DuckDB, Trino, and much more
Java, SQL and jOOQ.by lukaseder15 Dec 2023
New Dialects It’s been a few releases since we’ve added support for new dialects, but finally some very interesting RDBMS of increasing popularity have joined the jOOQ family including: - DuckDB...
The JDBC driver Maven Central coordinates for the most popular RDBMS
Java, SQL and jOOQ.by lukaseder13 Dec 2023
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.
Like repositories, jOOQ's DAOs are only useful for very simple stuff
Java, SQL and jOOQ.by lukaseder6 Dec 2023
jOOQ’s DAO API is one of jOOQ’s most controversial features. When it was first implemented, it was implemented merely: - Because it was so easy to implement - Because it seemed so useful for simple...
The JDBC Connection URLs and driver names of the most popular RDBMS
Java, SQL and jOOQ.by lukaseder1 Dec 2023
Need to connect to your RDBMS with JDBC and don’t have the JDBC connection URL or driver name at hand?
A simple example configuration to generate package private jOOQ code
Java, SQL and jOOQ.by lukaseder28 Jun 2023
Java’s package private visibility is an underrated feature. When you omit any visibility modifier in Java, then the default (for most objects) is package private, i.e.
Use jOOQ's code generator to easily pass table-valued parameters
Java, SQL and jOOQ.by lukaseder25 Apr 2023
Microsoft T-SQL supports a language feature called table-valued parameter (TVP), which is a parameter of a table type that can be passed to a stored procedure or function.
How to create hierarchies of Java objects from flat lists with Collector
Java, SQL and jOOQ.by lukaseder24 Mar 2023
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...