InMy.Live

De temps en temps, je rencontre un cas d’utilisation pour l’arcane NATURAL JOIN opérateur SQL, et je suis encore plus ravi quand je peux en faire un NATURAL FULL JOIN. Quelques articles de blog passés sur le sujet incluent:

Récemment, je suis tombé sur une question sur Reddit :

Existe-t-il une telle chose qui peut joindre deux tables complètement différentes sans relation mais agit comme une union ?

Au début, j’ai pensé à la UNION CORRESPONDING syntaxe, qui n’existe pas vraiment dans la plupart des dialectes SQL, même si c’est une fonctionnalité standard. Mais ensuite, je me suis souvenu que c’était à nouveau un cas d’utilisation parfait pour NATURAL FULL JOIN, cette fois légèrement différemment de l’exemple ci-dessus où deux tables sont comparées pour le contenu. Cette fois, nous voulons nous assurer que les deux tables jointes n’ont jamais de lignes correspondantes, afin d’obtenir le UNION comme comportement.

Prenons la base de données Sakila. Dans cette base de données, nous avons 3 tables contenant des personnes, dont :

Les tables sont définies comme suit :

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 customer (
  customer_id integer NOT NULL PRIMARY KEY,
  store_id integer NOT NULL,
  first_name varchar(45) NOT NULL,
  last_name varchar(45) NOT NULL,
  email varchar(50),
  address_id integer NOT NULL,
  active boolean NOT NULL,
  create_date date NOT NULL,
  last_update timestamp
);

CREATE TABLE staff (
  staff_id integer NOT NULL,
  first_name varchar(45) NOT NULL,
  last_name varchar(45) NOT NULL,
  address_id integer NOT NULL,
  email varchar(50),
  store_id integer NOT NULL,
  active boolean NOT NULL,
  username varchar(16) NOT NULL,
  password varchar(40),
  last_update timestamp NOT NULL,
  picture bytea
);

Comme on peut le voir, les colonnes communes ne sont en réalité que (FIRST_NAME, LAST_NAME, LAST_UPDATE), toutes les autres colonnes sont spécifiques à une table. En utilisant la requête suivante, nous pouvons concaténer toutes les données :

SELECT *
FROM (SELECT 'actor' AS source, * FROM actor) AS a
NATURAL FULL JOIN (SELECT 'customer' AS source, * FROM customer) AS c
NATURAL FULL JOIN (SELECT 'staff' AS source, * FROM staff) AS s;

Le résultat ressemble à ceci :

|source  |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       |...|           |   |        |   |
|customer|MARY      |SMITH    |2006-02-15 04:57:20.000|        |   |1          |...|        |   |
|customer|PATRICIA  |JOHNSON  |2006-02-15 04:57:20.000|        |   |2          |...|        |   |
|customer|LINDA     |WILLIAMS |2006-02-15 04:57:20.000|        |   |3          |...|        |   |
|staff   |Mike      |Hillyer  |2006-02-15 04:57:16.000|        |   |           |   |1       |...|
|staff   |Jon       |Stephens |2006-02-15 04:57:16.000|        |   |           |   |2       |...|

Quelques remarques :

• Les colonnes correspondantes (c’est-à-dire les colonnes portant le même nom) du NATURAL JOIN sont au début. Ils comprennent le synthétique SOURCE colonne, qui est différente pour chaque source de jointure, nous n’avons donc jamais de correspondance, ce qui était souhaité. Nous voulons UNION sémantique (c’est-à-dire concaténer les 3 tables), ne pas les faire correspondre.
• Les colonnes qui sont uniques à chaque table sont listées par la suite. Ils ne contiennent des données que s’ils appartiennent à la SOURCE

Cette technique n’est évidemment pas de tous les jours, mais elle peut être utile ponctuellement. Ne sous-estimez donc pas les pouvoirs de NATURAL FULL JOIN

Mises en garde

J’ai un peu triché. Le schéma réel de la base de données Sakila présente un conflit entre CUSTOMER et STAFF les tables:

CREATE TABLE customer (
  customer_id integer NOT NULL PRIMARY KEY,
  store_id integer NOT NULL,
  first_name varchar(45) NOT NULL,
  last_name varchar(45) NOT NULL,
  email varchar(50),
  address_id integer NOT NULL,
  activebool boolean NOT NULL,
  create_date date NOT NULL,
  last_update timestamp
  active integer NOT NULL, -- This is an integer
);

Avec cette définition de table, j’ai eu cette erreur pour ma requête :

Erreur SQL [42804]: ERREUR : les types JOIN/USING entier et booléen ne peuvent pas correspondre

Donc, pour résoudre ce problème, j’ai patché le CUSTOMER définition de table :

-- Patch
WITH customer AS (
  SELECT 
    customer_id, 
    store_id, 
    first_name, 
    last_name, 
    email, 
    address_id, 
    activebool as active, 
    create_date, 
    last_update 
  FROM customer
)

-- Original query
SELECT *
FROM (SELECT 'actor' AS source, * FROM actor) AS a
NATURAL FULL JOIN (SELECT 'customer' AS source, * FROM customer) AS c
NATURAL FULL JOIN (SELECT 'staff' AS source, * FROM staff) AS s;

En souhaitant que BigQuery soit utile * REPLACE (...) la syntaxe était plus largement disponible.