Wednesday, March 22, 2023
HomeJavaA Fast and Soiled Option to Concatenate Two Vaguely Associated Tables in...

A Fast and Soiled Option to Concatenate Two Vaguely Associated Tables in SQL – Java, SQL and jOOQ.


Now and again I run throughout a use case for the arcane NATURAL JOIN SQL operator, and I’m much more delighted once I could make {that a} NATURAL FULL JOIN. A couple of previous weblog posts on the topic embrace:

Lately, I stumbled upon a query on Reddit:

Is there such a factor that may be a part of two fully totally different tables with no relation however acts like a union?

At first I although of the UNION CORRESPONDING syntax, which doesn’t actually exist in most SQL dialects, even when it’s a normal characteristic. However then, I remembered that that is once more an ideal use case for NATURAL FULL JOIN, this time barely in another way from the above instance the place two tables are in contrast for contents. This time, we wish to make sure that the 2 joined tables by no means have matching rows, with the intention to get the UNION like behaviour.

Think about the Sakila database. In that database, we’ve got 3 tables containing individuals, 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,
  e-mail varchar(50),
  address_id integer NOT NULL,
  energetic boolean NOT NULL,
  create_date date NOT NULL,
  last_update timestamp
);

CREATE TABLE employees (
  staff_id integer NOT NULL,
  first_name varchar(45) NOT NULL,
  last_name varchar(45) NOT NULL,
  address_id integer NOT NULL,
  e-mail varchar(50),
  store_id integer NOT NULL,
  energetic boolean NOT NULL,
  username varchar(16) NOT NULL,
  password varchar(40),
  last_update timestamp NOT NULL,
  image bytea
);

As might be seen, the widespread columns are actually solely (FIRST_NAME, LAST_NAME, LAST_UPDATE), all the opposite columns are table-specific. Utilizing the next question, we are able to 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 'employees' AS supply, * FROM employees) AS s;

The consequence appears to be like 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          |...|        |   |
|employees   |Mike      |Hillyer  |2006-02-15 04:57:16.000|        |   |           |   |1       |...|
|employees   |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 in the beginning. They embrace the artificial SOURCE column, which is totally different for every be a part of supply, so we by no means have a match, which was desired. We wish UNION semantics (i.e. concatenate the three tables), not match them.
  • The columns which are distinctive to every tables are listed afterwards. They comprise knowledge provided that they belong to the related SOURCE

This method is clearly not for each day, however it may be sometimes helpful. So don’t underestimate the powers of NATURAL FULL JOIN

Caveats

I cheated just a little 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,
  e-mail varchar(50),
  address_id integer NOT NULL,
  activebool boolean NOT NULL,
  create_date date NOT NULL,
  last_update timestamp
  energetic integer NOT NULL, -- That is an integer
);

With this desk definition, I obtained this error for my question:

SQL Error [42804]: ERROR: JOIN/USING varieties 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, 
    e-mail, 
    address_id, 
    activebool as energetic, 
    create_date, 
    last_update 
  FROM buyer
)

-- Authentic 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 'employees' AS supply, * FROM employees) AS s;

Wishing that BigQuery’s helpful * REPLACE (...) syntax was extra broadly accessible.

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments