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/
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/
Need to connect to your RDBMS with JDBC and don't have the JDBC connection URL or driver name at hand? No problem, just look up your RDBMS below: // BigQuery driver = "com.simba.googlebigquery.jd...
https://blog.jooq.org/jdbc-connection-urls-of-the-most-popular-rdbms/
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/
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/
Probably the hardest thing to standardise in SQL is RETURNING data from DML statements. In this article, we'll look at various ways of doing that with jOOQ, in many of jOOQ's supported dialects, ...
https://blog.jooq.org/the-many-ways-to-return-data-from-sql-dml/
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/
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 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/
A long standing feature request has seen little love from the jOOQ
community, despite a lot of people probably wanting it. It goes by the
unimpressive title Let Table
https://blog.jooq.org/projecting-type-safe-nested-tablerecords-with-jooq-3-17/
I've recently stumbled upon this interesting Stack Overflow question about Hibernate's popular MultipleBagFetchException. The question is super popular, and the answers are plenty. The various li...
https://blog.jooq.org/no-more-multiplebagfetchexception-thanks-to-multiset-nested-collections/
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/
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/
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...
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/
I've run across a fun little trick to simulate latency in your development environments when testing some SQL queries. Possible use-cases including to validate that backend latency won't bring do...
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/
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/
SQL Server supports transforming flat tabular SQL result sets into hierarchical structures by convention using the convenient FOR XML or FOR JSON syntaxes. This is really convenient and less verb...
https://blog.jooq.org/using-sql-server-for-xml-and-for-json-syntax-on-other-rdbms-with-jooq/
The SQL MERGE statement is a device whose mystery is only exceeded by its power. A simple example shows its full power according to standard SQL. Imagine you have a production table for product p...
https://blog.jooq.org/the-many-flavours-of-the-arcane-sql-merge-statement/
It appears that our recent beginner SQL articles explaining SQL syntax were quite popular. These include: A Beginner’s Guide to the True Order of SQL Operations A Probably Incomplete, Comprehen...
https://blog.jooq.org/better-understand-sql-by-adding-optional-parentheses/
A very common misconception I often encounter with SQL users is the idea that DISTINCT is something like a function, and that it can take parenthesised arguments. Just recently, I've seen this St...
It's been a while since I've ranted on this blog, but I was recently challenged by a reddit thread to write about this topic, so here goes... So, you're writing a service that produces some JSON ...
https://blog.jooq.org/stop-mapping-stuff-in-your-middleware-use-sqls-xml-or-json-operators-instead/
One of Java's big strengths, in my opinion, is the fact that most naming conventions have been established by the creators of the language. For example: Class names are in PascalCase Member names...
Dogfooding, or eating your own dog food, is a practice that all product developers should implement all the time. According to wikipedia: Dogfooding, occurs when an organization uses its own prod...
This post is part of a new blog series about database migrations, which will cover a variety of database change management topics. In the near future, we'll look much more into these topics, hopi...
https://blog.jooq.org/how-to-simulate-a-liquibase-migration-using-h2/
MySQL 8 does not yet support the BOOLEAN type as specified in the SQL standard. There is a DDL "type" called BOOL, which is just an alias for TINYINT: create table t(b bool); select table_name, c...
https://blog.jooq.org/how-to-map-mysqls-tinyint1-to-boolean-in-jooq/
One of the biggest and undead myths in SQL is that COUNT(*) is faster than COUNT(1). Or was it that COUNT(1) is faster than COUNT(*)? Impossible to remember, because there's really no reason at a...
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 da...
https://blog.jooq.org/oracles-binary_double-can-be-much-faster-than-number/
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 ...
https://blog.jooq.org/using-distinct-on-in-non-postgresql-databases/
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 ex...
https://blog.jooq.org/quantified-like-any-predicates-in-jooq-3-12/