Une illustration de W3schools :

---

---

---

---

---

Qu’est-ce que SQL JOIN ?

SQL JOIN est une méthode permettant d’extraire des données de plusieurs tables de firebase database.

Quels sont les différents SQL JOIN s?

Il y a un total de cinq JOIN . Elles 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 ou INNER JOIN:

Dans ce type de JOIN , nous obtenons tous les enregistrements correspondant à 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é sur le seul fait que: SEULES les entrées correspondantes dans les deux tables DEVRAIENT être listées.

Notez qu’une JOIN sans autre JOIN clé JOIN (comme INNER , OUTER , LEFT , etc.) est une INNER JOIN . En d’autres termes, JOIN est un sucre syntaxique pour INNER JOIN (voir: Différence entre JOIN et INNER JOIN ).

2. REJOIGNEZ-VOUS:

OUTER JOIN récupère

Soit les lignes correspondantes d’une table et toutes les lignes de l’autre table, soit toutes les lignes de toutes les tables (qu’il y ait ou non une correspondance).

Il existe trois types de jointure externe:

2.1 LEFT OUTER JOIN ou LEFT JOIN

Cette jointure renvoie toutes les lignes de la table de gauche conjointement avec les lignes correspondantes de la table de droite. Si aucune colonne ne correspond à la table de droite, elle renvoie des valeurs NULL .

2.2 RIGHT OUTER JOIN ou RIGHT JOIN

Ce JOIN retourne toutes les lignes de la table de droite en conjonction avec les lignes correspondantes de la table de gauche. Si aucune colonne ne correspond à la table de gauche, elle renvoie des valeurs NULL .

2.3 FULL OUTER JOIN ou FULL JOIN

Cette LEFT OUTER JOIN combine LEFT OUTER JOIN et RIGHT OUTER JOIN . Il renvoie les lignes de l’une ou l’autre table lorsque les conditions sont remplies et renvoie la valeur NULL lorsqu’il n’y a pas de correspondance.

En d’autres termes, OUTER JOIN est basé sur le fait que: SEULES les entrées correspondantes dans UNE des tables (RIGHT ou LEFT) ou BOTH des tables (FULL) DEVRAIENT être listées.

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

3. NATURAL JOIN:

Il est basé sur les deux conditions:

1. le JOIN est effectué sur toutes les colonnes du même nom pour l’égalité.
2. Supprime les colonnes en double du résultat.

Cela semble être plus de nature théorique et, par conséquent (probablement), la plupart des SGBD ne prennent même pas la peine de le faire.

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 (ou le moins, pour être précis).

5. SELF JOIN:

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

JOIN sur la base des opérateurs

Selon l’opérateur utilisé pour une clause JOIN , il peut exister deux types de JOIN . Elles sont

1. Equi JOIN
2. Theta JOIN

1. Equi JOIN:

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

2. Theta JOIN:

Ceci est identique à EQUI JOIN mais permet tous les autres opérateurs comme>, <,> = etc.

Beaucoup considèrent EQUI JOIN et Theta JOIN similaires à INNER , OUTER etc. Mais je crois fermement que c’est une erreur et rend les idées vagues. Comme INNER JOIN , OUTER JOIN etc sont tous liés aux tables et à leurs données, EQUI JOIN et THETA JOIN sont uniquement connectés aux opérateurs que nous utilisons dans les premiers.

Encore une fois, nombreux sont ceux qui considèrent NATURAL JOIN comme une sorte d’ EQUI JOIN «particulière». En fait, c’est vrai, à cause de la première condition que j’ai mentionnée pour NATURAL JOIN . Cependant, nous n’avons pas à limiter cela à NATURAL JOIN s seul. INNER JOIN s, OUTER JOIN s etc pourrait également être un EQUI JOIN .

Définition:

---

JOINS est un moyen d’interroger les données combinées à partir de plusieurs tables simultanément.

Types de JOINS:

---

Concernant le SGBDR, il existe 5 types de jointure:

• Equi-Join: Combine les enregistrements communs de deux tables en fonction des conditions d’égalité. Techniquement, Join faite en utilisant l’opérateur d’égalité (=) pour comparer les valeurs de PrimaryKey d’une table et les valeurs de clé Foriegn de la table d’anthéro, ainsi l’ensemble de résultats inclut les enregistrements communs (appariés) des deux tables. Pour l’implémentation, voir INNER-JOIN.

• Natural-Join: Il s’agit d’une version améliorée d’Equi-Join, dans laquelle l’opération SELECT omet la colonne en double. Pour l’implémentation, voir INNER-JOIN

• Non-Equi-Join: Il est inverse d’Equi-join où la condition de jointure est utilisée par un opérateur différent (=), par exemple,! =, <=,> =,>, Ou BETWEEN.

• Self-Join:: Un comportement personnalisé de la jointure où une table combinée avec elle-même; Cela est généralement nécessaire pour interroger les tables auto-référencées (ou l’entité relationnelle Unary). Pour l’implémentation, voir INNER-JOINs.

• Produit cartésien: Il combine tous les enregistrements des deux tables sans aucune condition. Techniquement, il retourne un ensemble de résultats d’une requête sans clause WHERE.

En ce qui concerne le développement et la préoccupation SQL, il existe 3 types de jointures et toutes les jointures de SGBDR peuvent être obtenues en utilisant ces types de jointure.

1. INNER-JOIN: Il fusionne (ou combiens) les lignes correspondantes de deux tables. La correspondance est effectuée en fonction des colonnes de tables communes et de leur opération de comparaison. Si équité basée condition alors: EQUI-JOIN effectuée, sinon non-EQUI-Join.

2. ** OUTER-JOIN: ** Il fusionne (ou combine) les lignes correspondantes de deux tables et les lignes sans correspondance avec les valeurs NULL. Toutefois, vous pouvez personnaliser la sélection de lignes non appariées, par exemple en sélectionnant des lignes non appariées de la première ou de la deuxième table par sous-types: LEFT OUTER JOIN et RIGHT OUTER JOIN.

   2.1. LEFT Outer JOIN (aka, LEFT-JOIN): Renvoie les lignes correspondantes sous la forme de deux tables et non associées à la table LEFT (c’est-à-dire la première table) uniquement.

   2.2. RIGHT Outer JOIN (aka, RIGHT-JOIN): Renvoie les lignes correspondantes de deux tables et non égalées de la table RIGHT uniquement.

   2.3. FULL OUTER JOIN (aka OUTER JOIN): Renvoie la correspondance et la non correspondance des deux tables.

3. CROSS-JOIN: Cette jointure ne fusionne pas / combiens à la place, elle effectue un produit cartisien.

Remarque: Self-JOIN peut être obtenu par INNER-JOIN, OUTER-JOIN et CROSS-JOIN en fonction des besoins, mais la table doit se joindre à 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 Natural-JOIN

 Select A.*, B.Col1, B.Col2 --But no B.ForiengKyeColumn in Select FROM Table1 A INNER JOIN Table2 B On A.Pk = B.Fk; 

1.3: INNER-JOIN avec l’implémentation NON-Eqijoin

 Select * FROM Table1 A INNER JOIN Table2 B On A.Pk <= B.Fk; 

1.4: INNER-JOIN avec SELF-JOIN

 Select * FROM Table1 A1 INNER JOIN Table1 A2 On A1.Pk = A2.Fk; 

2.1: OUTER JOIN (jointure externe complète)

 Select * FROM Table1 A FULL OUTER JOIN Table2 B On A.Pk = B.Fk; 

2.2: GAUCHE

 Select * FROM Table1 A LEFT OUTER JOIN Table2 B On A.Pk = B.Fk; 

2.3: RIGHT JOIN

 Select * FROM Table1 A RIGHT OUTER JOIN Table2 B On A.Pk = B.Fk; 

3.1: CROSS JOIN

 Select * FROM TableA CROSS JOIN TableB; 

3.2: CROSS JOIN-Self JOIN

 Select * FROM Table1 A1 CROSS JOIN Table1 A2; 

//OU//

 Select * FROM Table1 A1,Table1 A2; 

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

• Ils se concentrent sur les formes de base de l’adhésion seulement
• Ils (ab) utilisent des diagrammes de Venn, qui sont un outil imprécis pour visualiser les jointures (ils sont beaucoup mieux pour les unions) .

J’ai récemment écrit un article sur le sujet: Un guide complet et probablement incomplet sur les nombreuses façons de rejoindre des tables en SQL , que je résumerai ici.

Avant toute chose: les JOIN sont des produits cartésiens

C’est pourquoi les diagrammes de Venn les expliquent de manière si imprécise, car un JOIN crée un produit cartésien entre les deux tables jointes. Wikipedia l’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:

La 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 tables séparées par des virgules, nous obtiendrons la même chose:

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

INNER JOIN (Thêta-JOIN)

Un INNER JOIN est juste une CROSS JOIN filtrée 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 

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

( regardez l’article pour des exemples de résultats )

EQUI JOIN

Un type particulier de Theta-JOIN est equi JOIN, que nous utilisons le plus souvent. Le prédicat rejoint la clé primaire d’une table avec la clé étrangère d’une autre table. Si nous utilisons la firebase database Sakila à titre 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 

Cela combine tous les acteurs avec leurs films.

Ou encore, sur certaines bases de données:

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

La syntaxe 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.

NATURAL JOIN

D’autres réponses ont indiqué ce “type 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 particulier de Theta-JOIN ou INNER JOIN. NATURAL JOIN collecte simplement toutes les colonnes communes aux deux tables en cours de jointure et les jointures USING() ces colonnes. Ce qui n’est guère utile à cause des correspondances accidentelles (comme les colonnes LAST_UPDATE de la firebase database Sakila ).

Voici la syntaxe:

 SELECT * FROM actor NATURAL JOIN film_actor NATURAL JOIN film 

EXTERIEUR

Maintenant, OUTER JOIN est un peu différent de INNER JOIN car il crée une UNION de plusieurs produits cartésiens. Nous pouvons écrire:

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

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

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

OUTER JOIN décline en trois versions:

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

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

Syntaxes alternatives

Il existe des syntaxes historiques obsolètes dans Oracle et SQL Server, qui supportaient déjà OUTER JOIN avant que le standard SQL ait une syntaxe pour ceci:

 -- 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 ne fais qu’énumérer cela ici pour que vous puissiez le reconnaître à partir d’anciens messages de blog / code hérité.

Partitionné OUTER JOIN

Peu de personnes le savent, mais le standard SQL spécifie la partition OUTER JOIN partitionnée (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 

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 du côté partitionné de la jointure se retrouveront dans le résultat, peu importe si le JOIN correspondait quelque chose de «l’autre côté du JOIN». Longue histoire courte: Ceci est de remplir des données rares dans les rapports. Très utile!

SEMI JOIN

Sérieusement? Aucune autre réponse à cela? Bien sûr que non, car il n’a malheureusement pas de syntaxe native en SQL (tout comme ANTI JOIN ci-dessous). Mais nous pouvons utiliser IN() et EXISTS() , par exemple pour trouver tous les acteurs ayant joué dans des films:

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

Le WHERE a.actor_id = fa.actor_id agit en tant que prédicat de semi-jointure. Si vous ne le croyez pas, consultez les plans d’exécution, par exemple dans Oracle. Vous verrez que la firebase database exécute une opération SEMI JOIN, pas le prédicat EXISTS() .

ANTI JOIN

C’est juste le contraire de SEMI JOIN ( veillez à ne pas utiliser NOT IN cependant , car il a une mise en garde importante)

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 (particulièrement les personnes 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.

LATERAL JOIN

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

 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érateurs de revenus par acteur. Chaque fois que vous avez besoin d’une requête TOP-N-per-something, LATERAL JOIN sera votre ami. Si vous êtes un utilisateur SQL Server, vous connaissez ce type de JOIN sous le nom 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, c’est peut-être de la sortingche, car une expression LATERAL JOIN ou APPLY est vraiment 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 est seulement implémenté 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 MULTISET l’ensemble de lignes résultant dans la requête externe. La requête ci-dessous sélectionne tous les acteurs et pour chaque acteur collecte leurs films dans une collection nestede:

 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 plus de types de JOIN que les simples ” 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, arrêtez d’utiliser des diagrammes de Venn pour les illustrer.

Dans SQL Server, il existe différents types de JOINS.

1. CROSS JOIN
2. JOINTURE INTERNE
3. EXTERIEUR

Les jointures externes sont à nouveau divisées en 3 types

1. Join gauche ou Jointure externe gauche
2. Right Join ou Right Outer Join
3. Full Join ou Full Outer Join

JOIN ou INNER JOIN

 SELECT Name, Gender, Salary, DepartmentName FROM tblEmployee INNER JOIN tblDepartment ON tblEmployee.DepartmentId = tblDepartment.Id OR SELECT Name, Gender, Salary, DepartmentName FROM tblEmployee JOIN tblDepartment ON tblEmployee.DepartmentId = tblDepartment.Id 

LEFT JOIN ou LEFT OUTER JOIN

 SELECT Name, Gender, Salary, DepartmentName FROM tblEmployee LEFT OUTER JOIN tblDepartment ON tblEmployee.DepartmentId = tblDepartment.Id OR SELECT Name, Gender, Salary, DepartmentName FROM tblEmployee LEFT JOIN tblDepartment ON tblEmployee.DepartmentId = tblDepartment.Id 

RIGHT JOIN ou RIGHT OUTER JOIN

 SELECT Name, Gender, Salary, DepartmentName FROM tblEmployee RIGHT OUTER JOIN tblDepartment ON tblEmployee.DepartmentId = tblDepartment.Id OR SELECT Name, Gender, Salary, DepartmentName FROM tblEmployee RIGHT JOIN tblDepartment ON tblEmployee.DepartmentId = tblDepartment.Id 

FULL JOIN ou FULL OUTER JOIN

 SELECT Name, Gender, Salary, DepartmentName FROM tblEmployee FULL OUTER JOIN tblDepartment ON tblEmployee.DepartmentId = tblDepartment.Id OR SELECT Name, Gender, Salary, DepartmentName FROM tblEmployee FULL JOIN tblDepartment ON tblEmployee.DepartmentId = tblDepartment.Id 

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

Je vais pousser ma bête noire: le mot clé USING.

Si les deux tables des deux côtés de JOIN ont leurs clés étrangères correctement nommées (c’est-à-dire, même nom, pas seulement “id), alors cela peut être utilisé:

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

Je trouve cela très pratique, lisible et pas assez utilisé.