[ad_1]
I lately stumbled upon a regular 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 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 technique to try 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 end result may seem 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 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, in the event 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 method, that is just like a NATURAL JOIN
, which additionally tries to search out that intersection of columns to provide a be a 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 adjustments its projection (e.g. due to a desk column rename), the results of all such queries will change as effectively, and it may not even produce a syntax error, only a totally different end result.
In actual fact, 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 a part of utilizing LAST_UPDATE
by chance.
With joins, we will use JOIN .. USING (first_name, last_name)
to at the least specify by which shared column names we need to be a 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 | |... |... |
In actual fact, 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 occasions in different dialects earlier than. Maybe, it should ship to PostgreSQL sooner or later. A department has been labored on by Vik Fearing:
jOOQ may quickly assist it within the API / parser / translator:
[ad_2]