JOINTURE SQL et les différents types de Jointures

Qu'est-ce que SQL JOIN ?

SQL JOIN est une méthode pour récupérer les données à partir de deux ou plusieurs tables de base de données.

Quels sont les différents SQL JOINs ?

Il y a un total de cinq JOINs. Ils sont :

  1. JOIN or INNER JOIN
  2. OUTER JOIN

     2.1 LEFT OUTER JOIN or LEFT JOIN
     2.2 RIGHT OUTER JOIN or RIGHT JOIN
     2.3 FULL OUTER JOIN or FULL JOIN

  3. NATURAL JOIN
  4. CROSS JOIN
  5. SELF JOIN

1. REJOINDRE ou INNER JOIN :

Dans ce genre de JOIN, nous obtenons tous les enregistrements qui correspondent à la condition, dans les deux tables, et les enregistrements des deux tables qui ne correspondent pas, ne sont pas signalés.

En d'autres termes, INNER JOIN est basée sur le fait que: SEULES les entrées correspondantes dans les DEUX tables DOIVENT être indiqués.

Noter qu'un JOIN sans autre JOIN mots-clés (comme INNER, OUTER, LEFT, etc) est un INNER JOIN. En d'autres termes, JOIN est
un sucre Syntaxique pour INNER JOIN (voir: Différence entre les REJOINDRE et INNER JOIN).

2. JOINTURE EXTERNE :

OUTER JOIN récupère

Soit,
les paires de lignes d'une table et de toutes les lignes dans l'autre table
Ou,
toutes les lignes de toutes les tables (il n'importe pas de savoir si ou non il existe une correspondance).

Il existe trois types de Jointure Externe :

2.1 JOINTURE EXTERNE GAUCHE ou de GAUCHE à REJOINDRE

Cette jointure renvoie toutes les lignes de la table de gauche en conjonction avec la mise en correspondance des lignes de la
table de droite. Si il n'y a pas de colonnes correspondant dans le tableau de droite, il retourne NULL valeurs.

2.2 JOINTURE EXTERNE DROITE de DROITE ou de REJOINDRE

Ce JOIN renvoie toutes les lignes de la table de droite, en conjonction avec la mise en correspondance des lignes de la
table de gauche. Si il n'y a pas de colonnes correspondant dans la table de gauche, il retourne NULL valeurs.

2.3 JOINTURE EXTERNE COMPLÈTE ou COMPLÈTE de REJOINDRE

Ce JOIN combine LEFT OUTER JOIN et RIGHT OUTER JOIN. Il renvoie des lignes de tableau lorsque les conditions sont remplies et retourne NULL valeur quand il n'y a pas de match.

En d'autres termes, OUTER JOIN est basée sur le fait que: SEULES les entrées correspondantes dans l'UN DES tableaux (à DROITE ou à GAUCHE) ou les DEUX tableaux(COMPLET) DOIVENT être indiqués.

Note that `OUTER JOIN` is a loosened form of `INNER JOIN`.

3. JOINTURE NATURELLE :

Elle est basée sur deux conditions :

1. la JOIN est faite sur toutes les colonnes avec le même nom pour l'égalité.
2. Supprime les doublons de colonnes du résultat.

Cela semble être plus de nature théorique et comme un résultat (probablement) la plupart des SGBD
ne pensez même pas à l'appui de cette.

4. JOINTURE CROISÉE :

Il est le produit Cartésien des deux tables impliquées. Le résultat d'une CROSS JOIN ne fera pas de sens
dans la plupart des situations. En outre, nous n'avons pas besoin (ou les besoins du moins, pour être précis).

5. AUTO-JOINTURE :

Il n'est pas une forme différente de JOIN, c'est plutôt un JOIN (INNER, OUTER, etc) d'une table à elle-même.

Rejoint basée sur les Opérateurs

Selon l'opérateur utilisé pour un JOIN clause, il peut y avoir deux types de JOINs. Ils sont

1. Equi REJOINDRE
2. Théta-JOINTURE

1. Equi REJOINDRE :

Pour quelque JOIN type (INNER, OUTER, etc), si nous utilisons UNIQUEMENT de l'opérateur d'égalité (=), alors nous disons que
le JOIN est un EQUI JOIN.

2. Théta-JOINTURE :

Ce sont les mêmes que EQUI JOIN mais elle permet à tous les autres opérateurs comme >, <, >= etc.

Beaucoup considèrent à la fois EQUI JOIN et Thêta JOIN similaire à INNER, OUTER
etc JOINs. Mais je crois fermement que son erreur et fait de la
des idées vagues. Parce que INNER JOIN, OUTER JOIN etc sont tous connectés avec
les tables et leurs données, tandis que EQUI JOIN et THETA JOIN ne sont
connecté avec les opérateurs, nous utilisons dans l'ancien.

Encore une fois, nombreux sont ceux qui considèrent NATURAL JOIN comme une sorte de
"étrange" EQUI JOIN. En fait, il est vrai, à cause de la première
la condition je le disais pour NATURAL JOIN. Cependant, nous n'avons pas à
limiter simplement à NATURAL JOINs seul. INNER JOINs, OUTER JOINs
etc peut être un EQUI JOIN trop.

Il est intéressant de noter la plupart des autres réponses souffrent de ces deux problèmes:

• Ils mettent l'accent sur les formes de base de rejoindre seul
• Ils (ab)utilisation des diagrammes de Venn, qui sont inexactes outil pour visualiser les jointures (ils sont beaucoup mieux pour les syndicats).

J'ai récemment écrit un article sur le sujet: Probablement Incomplète, Guide Complet sur les Différentes Façons de JOINDRE des Tables en SQL, que je vais résumer ici.

D'abord et avant tout: les Jointures sont les produits cartésiens

C'est pourquoi des diagrammes de Venn les expliquer de manière inexacte, car une JOINTURE crée un produit cartésien entre les deux tables jointes. Wikipédia illustre bien:

La syntaxe SQL pour les produits cartésiens est CROSS JOIN. Par exemple:

SELECT *

-- This just generates all the days in January 2017
FROM generate_series(
  '2017-01-01'::TIMESTAMP,
  '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day',
  INTERVAL '1 day'
) AS days(day)

-- Here, we're combining all days with all departments
CROSS JOIN departments

Qui combine toutes les lignes d'une table avec toutes les lignes de l'autre table:

Source:

+--------+   +------------+
| day    |   | department |
+--------+   +------------+
| Jan 01 |   | Dept 1     |
| Jan 02 |   | Dept 2     |
| ...    |   | Dept 3     |
| Jan 30 |   +------------+
| Jan 31 |
+--------+

Résultat:

+--------+------------+
| day    | department |
+--------+------------+
| Jan 01 | Dept 1     |
| Jan 01 | Dept 2     |
| Jan 01 | Dept 3     |
| Jan 02 | Dept 1     |
| Jan 02 | Dept 2     |
| Jan 02 | Dept 3     |
| ...    | ...        |
| Jan 31 | Dept 1     |
| Jan 31 | Dept 2     |
| Jan 31 | Dept 3     |
+--------+------------+

Si nous suffit d'écrire une liste séparée par des virgules de tables, nous allons obtenir le même:

-- CROSS JOINing two tables:
SELECT * FROM table1, table2

JOINTURE INTERNE (Theta-JOINTURE)

Un INNER JOIN est juste un filtrée CROSS JOIN où le prédicat de filtre est appelé Theta dans l'algèbre relationnelle.

Par exemple:

SELECT *

-- Same as before
FROM generate_series(
  '2017-01-01'::TIMESTAMP,
  '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day',
  INTERVAL '1 day'
) AS days(day)

-- Now, exclude all days/departments combinations for
-- days before the department was created
JOIN departments AS d ON day >= d.created_at

Noter que le mot-clé INNER est facultatif (sauf dans MS Access).

(regardez l'article pour le résultat des exemples)

EQUI REJOINDRE

Un type spécial de Theta-JOINTURE est équi REJOINDRE, que nous utilisons le plus. Le prédicat se joint à la clé primaire d'une table avec la clé étrangère d'une autre table. Si nous utilisons la Sakila base de données à des fins d'illustration, nous pouvons écrire:

SELECT *
FROM actor AS a
JOIN film_actor AS fa ON a.actor_id = fa.actor_id
JOIN film AS f ON f.film_id = fa.film_id

Cette combine tous les acteurs avec leurs films.

Ou également, sur certaines bases de données:

SELECT *
FROM actor
JOIN film_actor USING (actor_id)
JOIN film USING (film_id)

La USING() syntaxe permet de spécifier une colonne qui doit être présent sur chaque côté d'une opération de JOINTURE de tables et crée un prédicat d'égalité sur les deux colonnes.

JOINTURE NATURELLE

D'autres réponses ont listé ce "type de JOINTURE" séparément, mais qui n'a pas de sens. C'est juste une syntaxe de la forme de sucre pour equi REJOINDRE, ce qui est un cas particulier de Theta-JOINTURE ou une JOINTURE INTERNE. JOINTURE NATURELLE tout simplement recueille tous colonnes qui sont communs aux deux tables jointes et rejoint USING() ces colonnes. Qui n'est presque jamais utile, en raison des accidents de matchs (comme LAST_UPDATE colonnes dans la Sakila base de données).

Voici la syntaxe:

SELECT *
FROM actor
NATURAL JOIN film_actor
NATURAL JOIN film

JOINTURE EXTERNE

Maintenant, OUTER JOIN est un peu différent de INNER JOIN car elle crée un UNION de plusieurs produits cartésiens. On peut écrire:

-- Convenient syntax:
SELECT *
FROM a LEFT JOIN b ON <predicate>

-- Cumbersome, equivalent syntax:
SELECT a.*, b.*
FROM a JOIN b ON <predicate>
UNION ALL
SELECT a.*, NULL, NULL, ..., NULL
FROM a
WHERE NOT EXISTS (
  SELECT * FROM b WHERE <predicate>
)

Personne ne veut écrire le dernier, donc nous écrire OUTER JOIN (ce qui est généralement mieux optimisé pour les bases de données).

Comme INNER, le mot-clé OUTER est facultatif, ici.

OUTER JOIN est disponible en trois saveurs:

• LEFT [ OUTER ] JOIN: La table de gauche de la JOIN expression est ajoutée à l'union, comme indiqué ci-dessus.
• RIGHT [ OUTER ] JOIN: La table de droite de la JOIN expression est ajoutée à l'union, comme indiqué ci-dessus.
• FULL [ OUTER ] JOIN: Les deux tables de la JOIN expression sont ajoutés à l'union, comme indiqué ci-dessus.

Tous ces éléments peuvent être combinés avec le mot-clé USING() ou avec NATURAL (En fait, j'ai eu un monde réel de cas d'utilisation pour un NATUREL FULL JOIN récemment)

Alternative syntaxes

Il y a quelques historique, obsolète syntaxes dans Oracle et SQL Server, qui a soutenu OUTER JOIN déjà avant le SQL standard a une syntaxe de cette:

-- Oracle
SELECT *
FROM actor a, film_actor fa, film f
WHERE a.actor_id = fa.actor_id(+)
AND fa.film_id = f.film_id(+)

-- SQL Server
SELECT *
FROM actor a, film_actor fa, film f
WHERE a.actor_id *= fa.actor_id
AND fa.film_id *= f.film_id

Cela dit, ne pas utiliser cette syntaxe. Je viens de lister ici de sorte que vous pouvez le reconnaître à partir de vieux billets de blog /legacy code.

Partitionné OUTER JOIN

Peu de gens le savent, mais la norme SQL spécifie partitionné OUTER JOIN (et Oracle met en œuvre). Vous pouvez écrire des choses comme ceci:

WITH

  -- Using CONNECT BY to generate all dates in January
  days(day) AS (
    SELECT DATE '2017-01-01' + LEVEL - 1
    FROM dual
    CONNECT BY LEVEL <= 31
  ),

  -- Our departments
  departments(department, created_at) AS (
    SELECT 'Dept 1', DATE '2017-01-10' FROM dual UNION ALL
    SELECT 'Dept 2', DATE '2017-01-11' FROM dual UNION ALL
    SELECT 'Dept 3', DATE '2017-01-12' FROM dual UNION ALL
    SELECT 'Dept 4', DATE '2017-04-01' FROM dual UNION ALL
    SELECT 'Dept 5', DATE '2017-04-02' FROM dual
  )
SELECT *
FROM days 
LEFT JOIN departments 
  PARTITION BY (department) -- This is where the magic happens
  ON day >= created_at

Parties du résultat:

+--------+------------+------------+
| day    | department | created_at |
+--------+------------+------------+
| Jan 01 | Dept 1     |            | -- Didn't match, but still get row
| Jan 02 | Dept 1     |            | -- Didn't match, but still get row
| ...    | Dept 1     |            | -- Didn't match, but still get row
| Jan 09 | Dept 1     |            | -- Didn't match, but still get row
| Jan 10 | Dept 1     | Jan 10     | -- Matches, so get join result
| Jan 11 | Dept 1     | Jan 10     | -- Matches, so get join result
| Jan 12 | Dept 1     | Jan 10     | -- Matches, so get join result
| ...    | Dept 1     | Jan 10     | -- Matches, so get join result
| Jan 31 | Dept 1     | Jan 10     | -- Matches, so get join result

Le point ici est que toutes les lignes de la partitionné côté de la jointure du vent dans le résultat, peu importe si le JOIN appariés quelque chose de "de l'autre côté de la JOINTURE". Longue histoire courte: C'est pour combler le peu de données dans les rapports. Très utile!

SEMI JOIN

Au sérieux? Pas d'autre réponse obtenu cela? Bien sûr que non, car il n'est pas originaire de la syntaxe SQL, malheureusement (tout comme ANTI JOINDRE ci-dessous). Mais nous pouvons utiliser IN() et EXISTS(), par exemple, pour rechercher tous les acteurs qui ont joué dans des films:

SELECT *
FROM actor a
WHERE EXISTS (
  SELECT * FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

La WHERE a.actor_id = fa.actor_id prédicat agit en tant que semi-prédicat de jointure. Si vous ne le croyez pas, consultez les plans d'exécution, par exemple dans Oracle. Vous verrez que la base de données exécute un DEMI opération de JOINTURE, pas la EXISTS() prédicat.

ANTI REJOINDRE

C'est tout le contraire de la SEMI-JOINTURE (attention à ne pas utiliser PAS si, car elle a une importante mise en garde)

Voici tous les acteurs sans films:

SELECT *
FROM actor a
WHERE NOT EXISTS (
  SELECT * FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

Certaines personnes (en particulier MySQL personnes) aussi écrire des ANTI REJOINDRE comme ceci:

SELECT *
FROM actor a
LEFT JOIN film_actor fa
USING (actor_id)
WHERE film_id IS NULL

Je pense que les raisons historiques, est la performance.

LATÉRALE REJOINDRE

OMG c'est trop cool. Je suis le seul à le mentionner? Voici un endroit frais requête:

SELECT a.first_name, a.last_name, f.*
FROM actor AS a
LEFT OUTER JOIN LATERAL (
  SELECT f.title, SUM(amount) AS revenue
  FROM film AS f
  JOIN film_actor AS fa USING (film_id)
  JOIN inventory AS i USING (film_id)
  JOIN rental AS r USING (inventory_id)
  JOIN payment AS p USING (rental_id)
  WHERE fa.actor_id = a.actor_id -- JOIN predicate with the outer query!
  GROUP BY f.film_id
  ORDER BY revenue DESC
  LIMIT 5
) AS f
ON true

Il va retrouver le TOP 5 des recettes de produire des films par acteur. Chaque fois que vous avez besoin d'un TOP-N-par-quelque chose de requête, LATERAL JOIN sera votre ami. Si vous êtes un Serveur SQL personne, alors vous savez ce JOIN type sous le nom de APPLY

SELECT a.first_name, a.last_name, f.*
FROM actor AS a
OUTER APPLY (
  SELECT f.title, SUM(amount) AS revenue
  FROM film AS f
  JOIN film_actor AS fa ON f.film_id = fa.film_id
  JOIN inventory AS i ON f.film_id = i.film_id
  JOIN rental AS r ON i.inventory_id = r.inventory_id
  JOIN payment AS p ON r.rental_id = p.rental_id
  WHERE fa.actor_id = a.actor_id -- JOIN predicate with the outer query!
  GROUP BY f.film_id
  ORDER BY revenue DESC
  LIMIT 5
) AS f

OK, peut-être que c'est de la triche, car un LATERAL JOIN ou APPLY expression est vraiment un "sous-requête en corrélation" qui produit plusieurs lignes. Mais si nous permettons de "sous-requêtes corrélées", on peut parler aussi...

MULTISET

Ce n'est vraiment mis en œuvre par Oracle et Informix (à ma connaissance), mais il peut être émulée dans PostgreSQL à l'aide de tableaux et/ou XML et SQL Server à l'aide de XML.

MULTISET produit une sous-requête en corrélation et les nids de l'ensemble des lignes de la requête externe. La requête ci-dessous permet de sélectionner tous les acteurs et pour chaque acteur de la collecte de leurs films dans une collection imbriquées:

SELECT a.*, MULTISET (
  SELECT f.*
  FROM film AS f
  JOIN film_actor AS fa USING (film_id)
  WHERE a.actor_id = fa.actor_id
) AS films
FROM actor

Comme vous l'avez vu, il y a plusieurs types de JOINTURE que la "ennuyeux" INNER, OUTER, et CROSS JOIN qui sont généralement mentionnés. Plus de détails dans mon article. Et s'il vous plaît, cessez d'utiliser des diagrammes de Venn pour l'illustrer.

Je vais pousser ma bête noire: à l'AIDE de mots clés.

Si les deux tables sur les deux côtés de la JOINTURE ont leurs clés étrangères nommé correctement (c'est à dire, même nom, et pas seulement "id), alors ce peut être utilisée:

SELECT ...
FROM customers JOIN orders USING (customer_id)

Je trouve cela très pratique, lisible, et ne les utilise pas assez souvent.