CORRESPONDING – Java, SQL and jOOQ.

I just lately stumbled upon a typical SQL function that was carried out, to my shock, in HSQLDB. The key phrase is CORRESPONDING, and it may be used with all set operations, together with UNION, INTERSECT, and EXCEPT.

Let’s have a look at the sakila database. It has 3 tables with individuals in it:

CREATE TABLE actor (
    actor_id integer NOT NULL PRIMARY KEY,
    first_name varchar(45) NOT NULL,
    last_name varchar(45) NOT NULL,
    last_update timestamp
);

CREATE TABLE buyer (
    customer_id integer NOT NULL PRIMARY KEY,
    store_id smallint NOT NULL,
    first_name varchar(45) NOT NULL,
    last_name varchar(45) NOT NULL,
    electronic mail varchar(50),
    address_id smallint NOT NULL,
    create_date date NOT NULL,
    last_update timestamp,
    lively boolean
);

CREATE TABLE employees (
    staff_id integer NOT NULL PRIMARY KEY,
    first_name varchar(45) NOT NULL,
    last_name varchar(45) NOT NULL,
    address_id smallint NOT NULL,
    electronic mail varchar(50),
    store_id smallint NOT NULL,
    lively boolean NOT NULL,
    username varchar(16) NOT NULL,
    password varchar(40),
    last_update timestamp,
    image blob
);

Comparable, however not the identical. What if we needed to get all of the “individuals” from our database? A technique to do this in any strange database product is:

SELECT first_name, last_name
FROM actor
UNION ALL
SELECT first_name, last_name
FROM buyer
UNION ALL
SELECT first_name, last_name
FROM employees
ORDER BY first_name, last_name

The outcome would possibly appear like this:

|first_name|last_name|
|----------|---------|
|AARON     |SELBY    |
|ADAM      |GOOCH    |
|ADAM      |GRANT    |
|ADAM      |HOPPER   |
|ADRIAN    |CLARY    |
|AGNES     |BISHOP   |
|AL        |GARLAND  |
|ALAN      |DREYFUSS |
|...       |...      |

Utilizing CORRESPONDING

Now, in HSQLDB, and in commonplace SQL, you should utilize CORRESPONDING for this sort of process. For instance:

SELECT *
FROM actor
UNION ALL CORRESPONDING
SELECT *
FROM buyer
UNION ALL CORRESPONDING
SELECT *
FROM employees
ORDER BY first_name, last_name

The result’s this:

|first_name|last_name|last_update            |
|----------|---------|-----------------------|
|AARON     |SELBY    |2006-02-15 04:57:20.000|
|ADAM      |GOOCH    |2006-02-15 04:57:20.000|
|ADAM      |GRANT    |2006-02-15 04:34:33.000|
|ADAM      |HOPPER   |2006-02-15 04:34:33.000|
|ADRIAN    |CLARY    |2006-02-15 04:57:20.000|
|AGNES     |BISHOP   |2006-02-15 04:57:20.000|
|AL        |GARLAND  |2006-02-15 04:34:33.000|
|ALAN      |DREYFUSS |2006-02-15 04:34:33.000|
|...       |...      |...                    |

So, what has occurred? The columns FIRST_NAME, LAST_NAME, and LAST_UPDATE are frequent to all three tables. In different phrases, when you run this question in opposition to the INFORMATION_SCHEMA in HSQLDB:

SELECT column_name
FROM information_schema.columns
WHERE table_name="ACTOR"
INTERSECT
SELECT column_name
FROM information_schema.columns
WHERE table_name="CUSTOMER"
INTERSECT
SELECT column_name
FROM information_schema.columns
WHERE table_name="STAFF"

You get precisely these 3 columns:

|COLUMN_NAME|
|-----------|
|FIRST_NAME |
|LAST_NAME  |
|LAST_UPDATE|

In different phrases, CORRESPONDING creates the intersection of columns among the many subqueries of a set operation (i.e. the “shared columns”), tasks these, and applies the set operation that projection. In a manner, that is just like a NATURAL JOIN, which additionally tries to seek out that intersection of columns to supply a be part of predicate. Nonetheless, NATURAL JOIN then tasks all the columns (or the union of the columns), not simply the shared ones.

Utilizing CORRESPONDING BY

Similar to NATURAL JOIN, this can be a dangerous operation. As quickly as one subquery modifications its projection (e.g. due to a desk column rename), the results of all such queries will change as properly, and it may not even produce a syntax error, only a completely different outcome.

Actually, within the above instance, we in all probability didn’t even care about that LAST_UPDATE column. It was included within the UNION ALL set operation by chance, identical to NATURAL JOIN would be part of utilizing LAST_UPDATE by chance.

With joins, we are able to use JOIN .. USING (first_name, last_name) to at the very least specify by which shared column names we wish to be part of the 2 tables. With CORRESPONDING, we are able to provide the non-obligatory BY clause for a similar objective:

SELECT *
FROM actor
UNION ALL CORRESPONDING BY (first_name, last_name)
SELECT *
FROM buyer
UNION ALL CORRESPONDING BY (first_name, last_name)
SELECT *
FROM employees
ORDER BY first_name, last_name;

This now produces solely the 2 desired columns:

|first_name|last_name|
|----------|---------|
|AARON     |SELBY    |
|ADAM      |GOOCH    |
|ADAM      |GRANT    |
|ADAM      |HOPPER   |
|ADRIAN    |CLARY    |
|AGNES     |BISHOP   |
|AL        |GARLAND  |
|ALAN      |DREYFUSS |
|...       |...      |

Actually, this fashion, we might even use the syntax meaningfully for INTERSECT and EXCEPT, e.g. to seek out prospects who share their names with an actor:

SELECT *
FROM actor
INTERSECT CORRESPONDING BY (first_name, last_name)
SELECT *
FROM buyer
ORDER BY first_name, last_name;

Producing:

|first_name|last_name|
|----------|---------|
|JENNIFER  |DAVIS    |

Different dialects

I haven’t encountered this syntax many occasions in different dialects earlier than. Maybe, it would ship to PostgreSQL sooner or later. A department has been labored on by Vik Fearing:

jOOQ would possibly quickly help it within the API / parser / translator:

https://github.com/jOOQ/jOOQ/points/5285