FirebirdSQL logo
 COMMENTSInstructions de procédure SQL (PSQL) 
Connexions avec des conditions explicites

Dans la syntaxe de jointure explicite, il y a une clause ON, avec une condition de jointure, qui peut contenir n’importe quelle expression logique, mais qui contient généralement une condition de comparaison entre les deux sources impliquées.

Très souvent, cette condition est un contrôle d’égalité (ou un ensemble de contrôles d’égalité réunis par l’instruction AND) utilisant l’instruction "=". De telles connexions sont appelées équi-connexions (les exemples du chapitre sur les connexions INNER et OUTER étaient des équi-connexions).

Exemples de connexions avec des conditions explicites :

/*
 * Un échantillon de tous les clients de la ville de Détroit qui ont
 * effectué un achat.
 */
SELECT *
FROM customers c
JOIN sales s ON s.cust_id = c.id
WHERE c.city = 'Detroit'

/*
 * Idem, mais inclut dans l'échantillon les clients qui
 * n'ont pas effectué d'achat.
 */
SELECT *
FROM customers c
LEFT JOIN sales s ON s.cust_id = c.id
WHERE c.city = 'Detroit'

/*
 * Pour que chaque femme choisisse un homme qui est plus petit qu'elle.
 * Les hommes qui ne correspondent pas,
 * ne seront pas inclus dans l'échantillon.
 */
SELECT
    m.fullname AS man,
    f.fullname AS woman
FROM males m
JOIN females f ON f.height > m.height

/*
 * Un échantillon de tous les élèves, de leur classe et de leur tuteur.
 * Les élèves sans mentor seront inclus dans l'échantillon.
 * Les élèves sans classe ne seront pas inclus dans l'échantillon.
 */
SELECT
    p.firstname,
    p.middlename,
    p.lastname,
    c.name,
    m.name
FROM pupils p
JOIN classes c ON c.id = p.class
LEFT JOIN mentors m ON m.id = p.mentor
Connexions par colonnes nommées

Les connexions équivalentes comparent souvent des colonnes qui ont le même nom dans les deux tables. Pour ces connexions, nous pouvons utiliser un deuxième type de connexion explicite, appelé Named Columns Joins. Les jointures de colonnes nommées sont réalisées à l’aide de la fonction USING, qui énumère uniquement les noms des colonnes.

Note

Les connexions avec des colonnes nommées ne sont disponibles que dans le dialecte 3.

Ainsi, l’exemple suivant :

SELECT *
FROM flotsam f
  JOIN jetsam j
    ON f.sea = j.sea AND f.ship = j.ship

peut être réécrit comme suit :

SELECT *
FROM flotsam
JOIN jetsam USING (sea, ship)

qui est considérablement plus courte.Le jeu résultant est légèrement différent, du moins lorsqu’on utilise "SELECT *" :

  • Le jeu de résultats avec la condition de jointure explicite dans la clause ON contiendra chacune des colonnes SEA et `SHIP deux fois : une fois pour la table FLOTSAM et une fois pour la table JETSAM. Il est évident qu’ils les auront et qu’ils auront les mêmes valeurs ;

  • Le résultat d’une jointure de colonnes nommées, utilisant la clause USING, contiendra ces colonnes une fois.

Si vous voulez obtenir toutes les colonnes dans le résultat de la jointure de colonnes nommée, réécrivez la requête comme suit :

SELECT f.*, j.*
FROM flotsam f
JOIN jetsam j USING (sea, ship)

Pour les connexions externes (OUTER) avec des colonnes nommées, il existe des nuances supplémentaires lors de l’utilisation de “SELECT *” ou d’un nom de colonne incomplet.Si une colonne d’une ligne d’une source ne correspond pas à une colonne d’une ligne d’une autre source, mais que le résultat doit quand même être inclus à cause des instructions LEFT, RIGHT ou FULL, alors la colonne fusionnée aura une valeur non NULL.C’est assez juste, mais maintenant vous ne pouvez pas dire de quel ensemble gauche, droite ou les deux proviennent cette valeur.Ceci est particulièrement trompeur lorsque les valeurs proviennent du côté droit de l’ensemble de données, car “*” affiche toujours pour les colonnes combinées les valeurs du côté gauche de l’ensemble de données, même si la connexion RIGHT est utilisée.

Le fait que ce soit un problème dépend de la situation.Si c’est le cas, utilisez l’approche “f.*, j.*” démontrée ci-dessus, où f et j sont les noms ou alias des deux sources.Ou bien il est préférable d’éviter complètement les * dans les requêtes sérieuses et de lister tous les noms de colonnes pour les ensembles à joindre.Cette approche présente l’avantage supplémentaire de vous obliger à réfléchir aux données que vous souhaitez récupérer et à leur origine.

Il vous incombe de vous assurer que les types de colonnes sont compatibles entre les sources liées, dont les noms sont listés dans l’instruction USING.Si les types sont compatibles mais pas égaux, Firebird les convertira en un type avec une gamme de valeurs plus large avant de les comparer.Ce sera également le type de données de la colonne fusionnée qui apparaîtra dans le jeu résultant si “SELECT *” ou un nom de colonne incomplet est utilisé.Les noms de colonnes complets conserveront toujours leur type de données d’origine.

Tip

Si vous utilisez les colonnes de jonction dans la condition de sélection WHERE lorsque vous liez des colonnes nommées, utilisez toujours les noms de colonnes spécifiés, sinon l’index de cette colonne ne sera pas utilisé.

SELECT 1 FROM t1 a JOIN t2 b USING(x) WHERE x = 0;
PLAN JOIN (A NATURAL, B INDEX (RDB$2))

mais

SELECT 1 FROM t1 a JOIN t2 b USING(x) WHERE a.x = 0; -- ou 'b.x'
PLAN JOIN (A INDEX (RDB$1), B INDEX (RDB$2))

SELECT 1 FROM t1 a JOIN t2 b USING(x) WHERE b.x = 0;
PLAN JOIN (A INDEX (RDB$1), B INDEX (RDB$2))

Le fait est que la colonne non spécifiée dans ce cas est implicitement remplacée par COALESCE(a.x, b.x). Cette astuce est utilisée pour éliminer l’ambiguïté dans les noms de colonnes, mais elle empêche également l’application de l’index.

Connexions naturels (NATURAL JOIN)

En prenant comme base les connexions de colonnes nommées, l’étape suivante est une connexion naturelle qui effectue une équipartition sur toutes les colonnes de table de droite et de gauche du même nom. Les types de données de ces colonnes doivent être compatibles.

Note

Les connexions naturels ne sont disponibles qu’en dialecte 3.

Syntaxe
<natural-join> ::= NATURAL [<join-type>] JOIN <source>

<join-type> ::= INNER | {LEFT | RIGHT | FULL} [OUTER]

Deux tables sont donnés :

CREATE TABLE TA (
    a BIGINT,
    s VARCHAR(12),
    ins_date DATE
);

CREATE TABLE TB (
    a BIGINT,
    descr VARCHAR(12),
    x FLOAT,
    ins_date DATE
);

La connexion naturelle des tables TA et TB se fera par la colonne a et ins_date et les deux instructions suivants donneront le même résultat :

SELECT *
FROM TA
NATURAL JOIN TB;

SELECT *
FROM TA
JOIN TB USING (a, ins_date);

Comme toutes les connexions, les connexions naturelles sont des connexions internes par défaut, mais vous pouvez les transformer en connexions externes en spécifiant LEFT, RIGHT ou FULL avant le mot-clé JOIN.

Important
Attention

Si aucune colonne du même nom n’est trouvée dans les deux tables sources, `CROSS JOIN' sera exécuté.

Connexion transversale (CROSS JOIN)

Connexion croisée ou produit cartésien.Chaque ligne du tableau de gauche est reliée à chaque ligne du tableau de droite.

Syntaxe
<cross-join> ::=
    <table-reference> [, <table-reference> ...]
  | <table-reference> CROSS JOIN <table-primary>

Notez que la syntaxe utilisant une virgule est obsolète.

La jonction croisée de deux ensembles est équivalente à leur jonction par une condition de tautologie (une condition qui est toujours vraie).

Les deux requêtes suivantes donneront le même résultat :

SELECT *
FROM TA
CROSS JOIN TB;

SELECT *
FROM TA
JOIN TB ON 1 = 1;

Les jointures croisées sont des jointures internes car elles sélectionnent les lignes pour lesquelles il existe une correspondance - il se trouve que toutes les lignes correspondent ! Une jointure croisée externe, si elle existait, n’ajouterait rien au résultat, car les jointures externes ajoutent des enregistrements pour lesquels il n’y a pas de correspondance, et ils n’existent pas dans la jointure croisée.

Les liaisons transversales sont rarement utiles, sauf si vous souhaitez obtenir une liste de toutes les combinaisons possibles de deux variables ou plus.Supposons que vous vendiez un produit disponible en différentes tailles, différentes couleurs et différents matériaux.Si les valeurs de chaque variable sont répertoriées dans leur propre tableau, cette requête renverra toutes les combinaisons :

SELECT
    m.name,
    s.size,
    c.name
FROM materials m
CROSS JOIN sizes s
CROSS JOIN colors c