Now and again I run throughout a use case for the arcane NATURAL JOIN
SQL operator, and I’m much more delighted after I could make {that a} NATURAL FULL JOIN
. A couple of previous weblog posts on the topic embody:
Lately, I stumbled upon a query on Reddit:
At first I although of the UNION CORRESPONDING
syntax, which doesn’t actually exist in most SQL dialects, even when it’s a regular characteristic. However then, I remembered that that is once more an ideal use case for NATURAL FULL JOIN
, this time barely in a different way from the above instance the place two tables are in contrast for contents. This time, we wish to ensure the 2 joined tables by no means have matching rows, with the intention to get the UNION
like behaviour.
Take into account the Sakila database. In that database, we have now 3 tables containing folks, together with:
The tables are outlined as follows:
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 NOT NULL
);
CREATE TABLE buyer (
customer_id integer NOT NULL PRIMARY KEY,
store_id integer NOT NULL,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
electronic mail varchar(50),
address_id integer NOT NULL,
lively boolean NOT NULL,
create_date date NOT NULL,
last_update timestamp
);
CREATE TABLE workers (
staff_id integer NOT NULL,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
address_id integer NOT NULL,
electronic mail varchar(50),
store_id integer NOT NULL,
lively boolean NOT NULL,
username varchar(16) NOT NULL,
password varchar(40),
last_update timestamp NOT NULL,
image bytea
);
As may be seen, the frequent columns are actually solely (FIRST_NAME, LAST_NAME, LAST_UPDATE)
, all the opposite columns are table-specific. Utilizing the next question, we will concatenate all the info:
SELECT *
FROM (SELECT 'actor' AS supply, * FROM actor) AS a
NATURAL FULL JOIN (SELECT 'buyer' AS supply, * FROM buyer) AS c
NATURAL FULL JOIN (SELECT 'workers' AS supply, * FROM workers) AS s;
The consequence appears one thing like this:
|supply |first_name|last_name|last_update |actor_id|...|customer_id|...|staff_id|...| |--------|----------|---------|-----------------------|--------|---|-----------|---|--------|---| |actor |PENELOPE |GUINESS |2006-02-15 04:34:33.000|1 |...| | | | | |actor |NICK |WAHLBERG |2006-02-15 04:34:33.000|2 |...| | | | | |actor |ED |CHASE |2006-02-15 04:34:33.000|3 |...| | | | | |buyer|MARY |SMITH |2006-02-15 04:57:20.000| | |1 |...| | | |buyer|PATRICIA |JOHNSON |2006-02-15 04:57:20.000| | |2 |...| | | |buyer|LINDA |WILLIAMS |2006-02-15 04:57:20.000| | |3 |...| | | |workers |Mike |Hillyer |2006-02-15 04:57:16.000| | | | |1 |...| |workers |Jon |Stephens |2006-02-15 04:57:16.000| | | | |2 |...|
Some observations:
- The matched columns (i.e. columns by the identical identify) of the
NATURAL JOIN
are at first. They embody the artificialSOURCE
column, which is totally different for every be part of supply, so we by no means have a match, which was desired. We would likeUNION
semantics (i.e. concatenate the three tables), not match them. - The columns which can be distinctive to every tables are listed afterwards. They include information provided that they belong to the related
SOURCE
This system is clearly not for daily, however it may be sometimes helpful. So don’t underestimate the powers of NATURAL FULL JOIN
Caveats
I cheated a bit bit. The precise Sakila database schema has a battle between CUSTOMER
and STAFF
tables:
CREATE TABLE buyer (
customer_id integer NOT NULL PRIMARY KEY,
store_id integer NOT NULL,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
electronic mail varchar(50),
address_id integer NOT NULL,
activebool boolean NOT NULL,
create_date date NOT NULL,
last_update timestamp
lively integer NOT NULL, -- That is an integer
);
With this desk definition, I acquired this error for my question:
SQL Error [42804]: ERROR: JOIN/USING sorts integer and boolean can’t be matched
So, to repair this, I patched the CUSTOMER
desk definition:
-- Patch
WITH buyer AS (
SELECT
customer_id,
store_id,
first_name,
last_name,
electronic mail,
address_id,
activebool as lively,
create_date,
last_update
FROM buyer
)
-- Unique question
SELECT *
FROM (SELECT 'actor' AS supply, * FROM actor) AS a
NATURAL FULL JOIN (SELECT 'buyer' AS supply, * FROM buyer) AS c
NATURAL FULL JOIN (SELECT 'workers' AS supply, * FROM workers) AS s;
Wishing that BigQuery’s helpful * REPLACE (...)
syntax was extra broadly accessible.