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 |parent_id|label | |---|---------|-------------------| |1 | |C: | |2 |1 |eclipse | |3 |2 |configuration | |4 |2 |dropins | |5 |2 |features | … Continue reading How to Turn a List of Flat Elements into a Hierarchy in Java, SQL, or jOOQ
Tag: sql
How to Write a Derived Table in 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_ID, count(*) books FROM BOOK GROUP BY AUTHOR_ID ) nested ORDER BY nested.books DESC In jOOQ: // Declare the … Continue reading How to Write a Derived Table in jOOQ
The Performance Impact of SQL’s FILTER Clause
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 aggregate function? https://twitter.com/Ivan73965858/status/1622487080088600576 As a quick reminder, FILTER is an awesome standard SQL extension to filter out values before aggregating them in SQL. … Continue reading The Performance Impact of SQL’s FILTER Clause
Emulating Window Functions in MySQL 5.7
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 them, you'll use them everywhere. Some of you poor souls are unfortunate enough to be stuck on MySQL 5.7, either of your own … Continue reading Emulating Window Functions in MySQL 5.7
When to Use jOOQ and When to Use Native SQL
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 is full of side by side examples of the same query, e.g. Using jOOQ: ctx.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count()) .from(AUTHOR) .join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID)) .groupBy(AUTHOR.ID, AUTHOR.FIRST_NAME, … Continue reading When to Use jOOQ and When to Use Native SQL
LATERAL is Your Friend to Create Local Column Variables in 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 ( SELECT actor_id, first_name || ' ' || last_name AS name FROM actor ) AS a JOIN film_actor AS fa USING (actor_id) GROUP BY actor_id, name ORDER … Continue reading LATERAL is Your Friend to Create Local Column Variables in SQL
How to Plot an ASCII Bar Chart with 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 fancy charts from your jOOQ results. But that requires you use jOOQ, and you may not be using jOOQ, because you're not … Continue reading How to Plot an ASCII Bar Chart with SQL
A Condition is a Field
Starting with jOOQ 3.17, the Condition type extends the Field<Boolean> type. Because, that's what the SQL standard thinks it is, in sorts: <boolean value expression> ::= <predicate> The exact definition contains intermediate rules, but you get the idea. A <predicate> (which is a Condition in jOOQ) can be used wherever a <boolean value expression> can … Continue reading A Condition is a Field
Using H2 as a Test Database Product with jOOQ
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, as this rank outperforms products like: CockroachDBIgniteSingle Store (previously MemSQL)Interbase (which was forked as Firebird)Ingres (which is a predecessor to the awesome PostgreSQL)Google BigTable … Continue reading Using H2 as a Test Database Product with jOOQ
Create Dynamic Views with jOOQ 3.17’s new Virtual Client Side Computed Columns
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's a computed column? A computed column is a column that is derived ("computed") from an expression. It cannot be written to. It works like … Continue reading Create Dynamic Views with jOOQ 3.17’s new Virtual Client Side Computed Columns