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. by means of aggregation or subqueries) Unnest data from arrays into tables But it is very unopinionated when it comes to function support. The ISO/IEC 9075-2:2023(E) 6.47 <array value expression> specifies concatenation … Continue reading When SQL Meets Lambda Expressions
Tag: sql
Think About SQL MERGE in Terms of a RIGHT JOIN
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. The following two statements are equivalent: -- Popular SELECT c.first_name, c.last_name, p.amount FROM customer AS c LEFT JOIN payment AS p ON c.customer_id … Continue reading Think About SQL MERGE in Terms of a RIGHT JOIN
Emulating SQL FILTER with Oracle JSON Aggregate Functions
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 SQLite Trino YugabyteDB The following aggregate function computes the number of rows per group which satifsy the FILTER clause: SELECT COUNT(*) FILTER (WHERE BOOK.TITLE LIKE 'A%'), COUNT(*) FILTER (WHERE … Continue reading Emulating SQL FILTER with Oracle JSON Aggregate Functions
Getting Top 1 Values Per Group in Oracle
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 (DENSE_RANK FIRST ORDER BY c DESC, actor_id), max(first_name) KEEP (DENSE_RANK FIRST ORDER BY c DESC, actor_id), max(last_name) KEEP (DENSE_RANK FIRST ORDER … Continue reading Getting Top 1 Values Per Group in Oracle
An Efficient Way to Check for Existence of Multiple Values in SQL
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 played in any films, instead of: SELECT count(*) FROM actor a JOIN film_actor fa USING (actor_id) WHERE a.last_name = … Continue reading An Efficient Way to Check for Existence of Multiple Values in SQL
A Hidden Benefit of Implicit Joins: Join Elimination
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. This means that while join elimination is a powerful feature of many RDBMS, it isn't part of jOOQ's feature … Continue reading A Hidden Benefit of Implicit Joins: Join Elimination
jOOQ 3.19’s new Explicit and Implicit to-many path joins
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 join path correlation What are these features? Many ORMs (e.g. JPA, Doctrine, jOOQ 3.11 and others) support "path joins" (they may have different names for … Continue reading jOOQ 3.19’s new Explicit and Implicit to-many path joins
Workaround for MySQL’s “can’t specify target table for update in FROM clause” Error
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 follows: SQL Error [1093] [HY000]: You can't specify target table 't' for update in FROM … Continue reading Workaround for MySQL’s “can’t specify target table for update in FROM clause” Error
Maven Coordinates of the most popular JDBC Drivers
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 versions directly on https://central.sonatype.com/ with parameters g:groupId a:artifactId, for example, the H2 database and driver: https://central.sonatype.com/search?q=g%3Acom.h2database+a%3Ah2 The list only includes drivers … Continue reading Maven Coordinates of the most popular JDBC Drivers
How to Turn a List of Flat Elements into a Hierarchy in Java, SQL, or jOOQ
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