CORRESPONDING – Java, SQL and jOOQ.

on

|

views

and

comments

[ad_1]

I just lately stumbled upon a regular 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 take 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,
    e-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,
    e-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
);

Related, however not the identical. What if we needed to get all of the “individuals” from our database? A method to try this in any abnormal 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 end result may 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 customary SQL, you should utilize CORRESPONDING for this type 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, in case you 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 approach, that is much like a NATURAL JOIN, which additionally tries to search out that intersection of columns to provide a be part of predicate. Nonetheless, NATURAL JOIN then initiatives the entire 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 adjustments its projection (e.g. due to a desk column rename), the results of all such queries will change as nicely, and it may not even produce a syntax error, only a totally different end result.

The truth is, 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 by chance, identical to NATURAL JOIN would be part of utilizing LAST_UPDATE by chance.

With joins, we will use JOIN .. USING (first_name, last_name) to no less than specify by which shared column names we need to be part of the 2 tables. With CORRESPONDING, we will 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 |
|...       |...      |

The truth is, this manner, we might even use the syntax meaningfully for INTERSECT and EXCEPT, e.g. to search out clients 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 would ship to PostgreSQL sooner or later. A department has been labored on by Vik Fearing:

jOOQ may quickly help it within the API / parser / translator:

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



[ad_2]

Supply hyperlink

Share this
Tags

Must-read

Google Presents 3 Suggestions For Checking Technical web optimization Points

Google printed a video providing three ideas for utilizing search console to establish technical points that may be inflicting indexing or rating issues. Three...

A easy snapshot reveals how computational pictures can shock and alarm us

Whereas Tessa Coates was making an attempt on wedding ceremony clothes final month, she posted a seemingly easy snapshot of herself on Instagram...

Recent articles

More like this

LEAVE A REPLY

Please enter your comment!
Please enter your name here