CORRESPONDING – Java, SQL and jOOQ.

I just lately stumbled upon a normal SQL characteristic that was applied, 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 wished to get all of the “individuals” from our database? A technique to try this in any unusual 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 consequence would possibly appear to be 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 customary SQL, you need to use CORRESPONDING for this sort of job. 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 widespread to all three tables. In different phrases, for those who run this question towards 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”), initiatives these, and applies the set operation that projection. In a method, that is just like a NATURAL JOIN, which additionally tries to seek out that intersection of columns to provide a be part of predicate. Nevertheless, NATURAL JOIN then initiatives the entire columns (or the union of the columns), not simply the shared ones.

Utilizing CORRESPONDING BY

Identical to NATURAL JOIN, it is a dangerous operation. As quickly as one subquery adjustments its projection (e.g. due to a desk column rename), the results of all such queries will change as properly, and it won’t even produce a syntax error, only a totally different consequence.

In truth, within the above instance, we most likely didn’t even care about that LAST_UPDATE column. It was included within the UNION ALL set operation accidentally, identical to NATURAL JOIN would be part of utilizing LAST_UPDATE accidentally.

With joins, we are able to use JOIN .. USING (first_name, last_name) to a minimum of specify by which shared column names we need to be part of the 2 tables. With CORRESPONDING, we are able to provide the elective BY clause for a similar goal:

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 |
|...       |...      |

In truth, this manner, we may 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 instances in different dialects earlier than. Maybe, it’s going to 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