Qu'est-ce qu'une jointure SQL, et quels sont les différents types?

Question

Qu'est-ce qu'une jointure SQL et quels sont les différents types ?

Answer 1

Une illustration de W3schools:

---

---

---

---

---

Answer 2

Qu'est-ce que SQL JOIN ?

SQL JOIN est une méthode permettant de récupérer des données de deux tables de base de données ou plus.

Quels sont les différents SQL JOIN ?

Il existe 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. JOIN or INNER JOIN :

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

En d'autres termes, INNER JOIN est basé sur le simple fait que : SEULEMENT les entrées correspondantes dans LES DEUX tables DOIVENT être listées.

Remarquez qu'un JOIN sans aucun autre mot clé de JOIN (comme INNER, OUTER, LEFT, etc) est un INNER JOIN. En d'autres termes, JOIN est du sucre syntaxique pour INNER JOIN (voir : Différence entre JOIN et INNER JOIN).

2. OUTER JOIN :

OUTER JOIN récupère

Soit, les lignes correspondantes d'une table et toutes les lignes de l'autre table Ou, toutes les lignes de toutes les tables (peu importe s'il y a une correspondance ou non).

Il existe trois types de Jointures Externes :

2.1 LEFT OUTER JOIN or LEFT JOIN

Cette jointure retourne toutes les lignes de la table de gauche avec les lignes correspondantes de la table de droite. S'il n'y a pas de colonnes correspondantes dans la table de droite, il renvoie des valeurs NULL.

2.2 RIGHT OUTER JOIN or RIGHT JOIN

Ce JOIN retourne toutes les lignes de la table de droite avec les lignes correspondantes de la table de gauche. S'il n'y a pas de colonnes correspondantes dans la table de gauche, il renvoie des valeurs NULL.

2.3 FULL OUTER JOIN or FULL JOIN

Ce JOIN combine les LEFT OUTER JOIN et RIGHT OUTER JOIN. Il renvoie des lignes de l'une des tables lorsque les conditions sont remplies et renvoie la valeur NULL en cas de non correspondance.

En d'autres termes, OUTER JOIN est basé sur le fait que : SEULEMENT les entrées correspondantes dans UNE DES tables (DROITE ou GAUCHE) ou LES DEUX tables (COMPLET) DOIVENT être listées.

Remarquez que `OUTER JOIN` est une forme assouplie de `INNER JOIN`.

3. NATURAL JOIN :

Il est basé sur les deux conditions :

1. le JOIN est fait sur toutes les colonnes ayant le même nom pour l'égalité.
2. Supprime les colonnes dupliquées du résultat.

Cela semble être plus théorique de nature et par conséquent (probablement) la plupart des SGBD ne se donnent même pas la peine de le prendre en charge.

4. CROSS JOIN :

C'est le produit cartésien des deux tables impliquées. Le résultat d'un CROSS JOIN n'aura aucun sens dans la plupart des situations. De plus, nous n'en aurons pas besoin du tout (ou le moins possible, pour être précis).

5. SELF JOIN :

Il ne s'agit pas d'une forme différente de JOIN, mais plutôt d'un JOIN (INNER, OUTER, etc) d'une table avec elle-même.

JOINTURES basées sur les opérateurs

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

1. Equi JOIN
2. Theta JOIN

1. Equi JOIN :

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

2. Theta JOIN :

C'est la même chose qu'un EQUI JOIN mais il permet tous les autres opérateurs comme >, <, >= etc.

Beaucoup considèrent à la fois l'EQUI JOIN et le Theta JOIN comme similaires aux INNER, OUTER etc JOINs. Mais je crois fermement que c'est une erreur et cela rend les idées floues. Parce que les INNER JOIN, OUTER JOIN etc sont tous connectés avec les tables et leurs données alors que l'EQUI JOIN et le THETA JOIN sont uniquement connectés avec les opérateurs que nous utilisons dans le premier cas.

Encore une fois, il y en a beaucoup qui considèrent le NATURAL JOIN comme une sorte de "EQUI JOIN" particulier. En fait, c'est vrai, à cause de la première condition que j'ai mentionnée pour le NATURAL JOIN. Cependant, nous ne devons pas limiter cela simplement aux seuls NATURAL JOINs. Les INNER JOINs, les OUTER JOINs etc pourraient également être un EQUI JOIN.

Answer 3

Définition:

---

JOINS sont un moyen de interroger les données qui sont combinées simultanément à partir de plusieurs tables.

Types de JOINS:

---

En ce qui concerne les RDBMS, il existe 5 types de joins:

• Equi-Join: Combine des enregistrements communs de deux tables basé sur une condition d'égalité. Techniquement, le Join est effectué en utilisant un opérateur d'égalité (=) pour comparer les valeurs de la clé primaire d'une table et les valeurs des clés étrangères d'une autre table, donc le jeu de résultats inclut des enregistrements communs (corrélés) des deux tables. Pour implémentation voir INNER JOIN.

• Natural-Join: C'est une version améliorée d'Equi-Join, dans laquelle l'opération SELECT omet les colonnes dupliquées. Pour implémentation voir INNER JOIN

• Non-Equi-Join: C'est l'opposé de l'Equi-Join où la condition de jointure utilise un opérateur autre que l'opérateur égal(=) par exemple, !=, <=, >=, >, < ou ENTRE autres. Pour implémentation voir INNER JOIN.

• Self-Join: Un comportement personnalisé de jointure où une table est combinée avec elle-même; Cela est généralement nécessaire pour interroger des tables faisant référence à elles-mêmes (ou une entité de relation unaire). Pour implémentation voir INNER JOINs.

• Produit Cartésien: Combine de manière croisée tous les enregistrements des deux tables sans condition. Techniquement, il renvoie le jeu de résultats d'une requête sans clause WHERE.

En ce qui concerne le SQL et l'avancement, il y a 3 types de jointures et toutes les jointures RDBMS peuvent être obtenues en utilisant ces types de jointures.

1. INNER JOIN: Fusionne (ou combine) les lignes correspondantes de deux tables. La correspondance se fait sur les colonnes communes des tables et leur opération de comparaison. Si la condition basée sur l'égalité alors : EQUI-JOIN est effectué, sinon c'est un Non-EQUI-Join.

2. OUTER JOIN: Fusionne (ou combine) les lignes correspondantes de deux tables et les lignes non correspondantes avec des valeurs NULL. Cependant, une sélection personnalisée des lignes non correspondantes peut être faite par exemple, sélectionner les lignes non correspondantes de la première table ou de la deuxième table par des sous-types: LEFT OUTER JOIN et RIGHT OUTER JOIN.

   2.1. LEFT Outer JOIN (alias, LEFT-JOIN): Renvoie les lignes correspondantes de deux tables et les non-correspondantes de la table de GAUCHE (c'est-à-dire la première table) uniquement.

   2.2. RIGHT Outer JOIN (alias, RIGHT-JOIN): Renvoie les lignes correspondantes de deux tables et les non-correspondantes de la table de DROITE uniquement.

   2.3. FULL OUTER JOIN (alias OUTER JOIN): Renvoie les lignes correspondantes et non-correspondantes des deux tables.

3. CROSS JOIN: Ce join ne fusionne pas/combine au contraire il effectue un produit cartésien.

Note: Un Self JOIN peut être obtenu en utilisant INNER JOIN, OUTER JOIN et CROSS JOIN en fonction des besoins mais la table doit être jointe avec elle-même.

Pour plus d'informations:

Exemples:

1.1: INNER-JOIN: Implémentation d'Equi-join

SELECT  *
FROM Table1 A 
 INNER JOIN Table2 B ON A. =B.;

1.2: INNER-JOIN: Implémentation de Natural-JOIN

Sélectionnez A.*, B.Col1, B.Col2          --Mais pas de B.ForeignKeyColumn dans Select
 FROM Table1 A
 INNER JOIN Table2 B On A.Pk = B.Fk;

1.3: INNER-JOIN avec NON-Equi-join implementation

Sélectionnez *
 FROM Table1 A INNER JOIN Table2 B On A.Pk <= B.Fk;

1.4: INNER-JOIN avec SELF-JOIN

Sélectionnez *
 FROM Table1 A1 INNER JOIN Table1 A2 On A1.Pk = A2.Fk;

2.1: OUTER JOIN (full outer join)

Sélectionnez *
 FROM Table1 A FULL OUTER JOIN Table2 B On A.Pk = B.Fk;

2.2: LEFT JOIN

Sélectionnez *
 FROM Table1 A LEFT OUTER JOIN Table2 B On A.Pk = B.Fk;

2.3: RIGHT JOIN

Sélectionnez *
 FROM Table1 A RIGHT OUTER JOIN Table2 B On A.Pk = B.Fk;

3.1: CROSS JOIN

Sélectionnez *
 FROM TableA CROSS JOIN TableB;

3.2: CROSS JOIN-Self JOIN

Sélectionnez *
 FROM Table1 A1 CROSS JOIN Table1 A2;

//OR//

Sélectionnez *
 FROM Table1 A1,Table1 A2;

Answer 4

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

• Ils se concentrent uniquement sur les formes de base de la jointure
• Ils (ab)utilisent les diagrammes de Venn, qui sont un outil inexact pour visualiser les jointures (ils sont bien meilleurs pour les unions). .

J'ai récemment écrit un article sur le sujet : Un guide complet, probablement incomplet, des différentes façons de JOINER des tables en SQL que je vais résumer ici.

Avant toute chose : Les JOINs sont des produits cartésiens

C'est pourquoi les diagrammes de Venn les expliquent de manière si inexacte, parce qu'une JOIN crée une produit cartésien entre les deux tables jointes. Wikipedia l'illustre joliment :

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 écrivons simplement une liste de tableaux séparés par des virgules, nous obtiendrons la même chose :

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

INNER JOIN (Theta-JOIN)

Un site INNER JOIN est juste un filtre CROSS JOIN où le prédicat du filtre est appelé Theta en 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

Notez que le mot-clé INNER est facultative (sauf dans MS Access).

( consultez l'article pour des exemples de résultats )

EQUI JOIN

Un type particulier de Theta-JOIN est equi JOIN, que nous utilisons le plus. Le prédicat joint la clé primaire d'une table avec la clé étrangère d'une autre table. Si nous utilisons le prédicat Base de données Sakila à titre d'illustration, on peut é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

Cela combine tous les acteurs avec leurs films.

Ou aussi, sur certaines bases de données :

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

Le site USING() permet de spécifier une colonne qui doit être présente de chaque côté des tables d'une opération JOIN et crée un prédicat d'égalité sur ces deux colonnes.

JOINT NATUREL

D'autres réponses ont listé ce "type de JOIN" séparément, mais cela n'a pas de sens. C'est juste une forme de sucre syntaxique pour equi JOIN, qui est un cas spécial de Theta-JOIN ou INNER JOIN. NATURAL JOIN rassemble simplement tous les colonnes qui sont communes aux deux tables jointes et les jointures USING() ces colonnes. Ce qui n'est presque jamais utile, à cause des correspondances accidentelles (comme les LAST_UPDATE dans les colonnes de l Base de données Sakila ).

Voici la syntaxe :

SELECT *
FROM actor
NATURAL JOIN film_actor
NATURAL JOIN film

OUTER JOIN

Maintenant, OUTER JOIN est un peu différent de INNER JOIN car il 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 cette dernière, donc nous écrivons OUTER JOIN (ce qui est généralement mieux optimisé par les bases de données).

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

OUTER JOIN existe en trois saveurs :

• LEFT [ OUTER ] JOIN : La table gauche du JOIN est ajoutée à l'union comme indiqué ci-dessus.
• RIGHT [ OUTER ] JOIN : La table de droite du JOIN est ajoutée à l'union comme indiqué ci-dessus.
• FULL [ OUTER ] JOIN : Les deux tableaux du JOIN 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 ( J'ai en fait un cas réel d'utilisation d'un NATURAL FULL JOIN récemment )

Syntaxes alternatives

Il y a quelques syntaxes historiques, dépréciées dans Oracle et SQL Server, qui supportaient OUTER JOIN déjà avant que la norme SQL ait une syntaxe pour cela :

-- 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, n'utilisez pas cette syntaxe. Je l'ai simplement listée ici pour que vous puissiez la reconnaître dans les anciens articles de blog / le code hérité.

Partitionné OUTER JOIN

Peu de gens le savent, mais le standard SQL spécifie des partitions. OUTER JOIN (et Oracle l'implémente). 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

Des 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

L'idée est que toutes les lignes de la partie partitionnée de la jointure se retrouveront dans le résultat, que la méthode de partitionnement soit ou non utilisée. JOIN correspond à tout ce qui se trouve de "l'autre côté du JOIN". Pour faire court : il s'agit de remplir les données éparses dans les rapports. Très utile !

SEMI JOIN

Sérieusement ? Aucune autre réponse n'a eu ça ? Bien sûr que non, parce qu'elle n'a pas de syntaxe native en SQL, malheureusement (tout comme ANTI JOIN ci-dessous). Mais nous pouvons utiliser IN() et EXISTS() pour trouver, par exemple, 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
)

Le site WHERE a.actor_id = fa.actor_id agit comme le prédicat de semi-jonction. Si vous ne le croyez pas, vérifiez les plans d'exécution, par exemple dans Oracle. Vous verrez que la base de données exécute une opération SEMI JOIN, et non pas l'opération EXISTS() prédicat.

ANTI JOIN

C'est exactement le contraire de SEMI JOIN ( veillez à ne pas utiliser NOT IN bien que car il comporte un avertissement important)

Voici tous les acteurs sans film :

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

Certaines personnes (en particulier les utilisateurs de MySQL) écrivent également ANTI JOIN comme ceci :

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

Je pense que la raison historique est la performance.

JOINT LATERAL

OMG, celui-là est trop cool. Je suis le seul à le mentionner ? Voici une requête cool :

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 trouvera le TOP 5 des films générant des revenus par acteur. Chaque fois que vous avez besoin d'une requête TOP-N-par-chose, LATERAL JOIN sera votre ami. Si vous êtes une personne de SQL Server, alors vous savez que JOIN 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, parce qu'un LATERAL JOIN ou APPLY est en réalité une "sous-requête corrélée" qui produit plusieurs lignes. Mais si on autorise les "sous-requêtes corrélées", on peut aussi parler de...

MULTISET

Ceci n'est réellement implémenté que par Oracle et Informix (à ma connaissance), mais il peut être émulé dans PostgreSQL en utilisant des tableaux et/ou XML et dans SQL Server en utilisant XML.

MULTISET produit une sous-requête corrélée et imbrique le jeu de lignes résultant dans la requête externe. La requête ci-dessous sélectionne tous les acteurs et, pour chacun d'eux, rassemble leurs films dans une collection imbriquée :

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 existe d'autres types de JOIN que le "classique". INNER , OUTER et CROSS JOIN qui sont habituellement mentionnés. Plus de détails dans mon article . Et s'il vous plaît, arrêtez d'utiliser des diagrammes de Venn pour les illustrer.

Answer 5

J'ai créé une illustration qui explique mieux que des mots, à mon avis :