jOOQ 3.15 introduced the concept of an ad-hoc converter, a converter that is applied "ad-hoc" to a single query. It uses the same underlying mechanisms as any ordinary Converter that is attached to generated code for use in every query. An example of such an ad-hoc converter is this: // Without the converter, assuming BOOK.ID … Continue reading How to use jOOQ’s Converters with UNION Operations
Tag: UNION
A Rarely Seen, but Useful SQL Feature: CORRESPONDING
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, INTERSECT, and EXCEPT. Let's look at the sakila database. It has 3 tables with people in it: CREATE TABLE actor ( actor_id integer NOT … Continue reading A Rarely Seen, but Useful SQL Feature: CORRESPONDING
Use NATURAL FULL JOIN to compare two tables in SQL
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); INSERT INTO t1 VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9); INSERT INTO t2 VALUES … Continue reading Use NATURAL FULL JOIN to compare two tables in SQL
How to Write Multiset Conditions With Oracle VARRAY Types
Oracle is one of the few databases that implements the SQL standard ORDBMS extensions, which essentially allow for nested collections. Other databases that have these features to some extent are CUBRID, Informix, PostgreSQL. Oracle has two types of nested collections: -- Nested tables CREATE TYPE t1 AS TABLE OF VARCHAR2(10); / -- Varrays CREATE TYPE … Continue reading How to Write Multiset Conditions With Oracle VARRAY Types
You Probably don’t Use SQL INTERSECT or EXCEPT Often Enough
When people talk about SQL JOIN, they often use Venn Diagrams to illustrate inclusion and exclusion of the two joined sets: While these Venn diagrams are certainly useful to understand (and remember) SQL JOIN syntax, they're not entirely accurate, because SQL JOIN is a special type of a cartesian product, the CROSS JOIN. In a … Continue reading You Probably don’t Use SQL INTERSECT or EXCEPT Often Enough