CROSS JOIN vs INNER JOIN en SQL

Question

Quelle est la différence entre CROSS JOIN y INNER JOIN ?

CROSS JOIN :

SELECT 
    Movies.CustomerID, Movies.Movie, Customers.Age, 
    Customers.Gender, Customers.[Education Level], 
    Customers.[Internet Connection], Customers.[Marital Status], 
FROM   
    Customers 
CROSS JOIN 
    Movies

INNER JOIN :

SELECT 
    Movies.CustomerID, Movies.Movie, Customers.Age, 
    Customers.Gender, Customers.[Education Level], 
    Customers.[Internet Connection], Customers.[Marital Status]
FROM   
    Customers 
INNER JOIN 
    Movies ON Customers.CustomerID = Movies.CustomerID

Laquelle est la meilleure et pourquoi devrais-je utiliser l'une ou l'autre ?

Answer 1

Voici le meilleur exemple de Cross Join et Inner Join.

Considérez les tableaux suivants

TABLEAU : Teacher

x------------------------x
| TchrId   | TeacherName | 
x----------|-------------x
|    T1    |    Mary     |
|    T2    |    Jim      |
x------------------------x

TABLEAU : Student

x--------------------------------------x
|  StudId  |    TchrId   | StudentName | 
x----------|-------------|-------------x            
|    S1    |     T1      |    Vineeth  |
|    S2    |     T1      |    Unni     |
x--------------------------------------x

1. INNER JOIN

La jointure interne sélectionne les lignes qui satisfont à la fois à la table .

Considérons que nous devons trouver les enseignants qui sont des professeurs de classe et leurs élèves correspondants. Dans cette condition, nous devons appliquer JOIN o INNER JOIN et va

Requête

SELECT T.TchrId,T.TeacherName,S.StudentName 
FROM #Teacher T
INNER JOIN #Student S ON T.TchrId = S.TchrId

• SQL FIDDLE

Résultat

x--------------------------------------x
|  TchrId  | TeacherName | StudentName | 
x----------|-------------|-------------x            
|    T1    |     Mary    |    Vineeth  |
|    T1    |     Mary    |    Unni     |
x--------------------------------------x

2. CROSS JOIN

La jointure croisée sélectionne toutes les lignes de la première table et toutes les lignes de la deuxième table et les montre comme produit cartésien, c'est-à-dire avec toutes les possibilités.

Si l'on considère que nous devons trouver tous les enseignants de l'école et les élèves indépendamment des enseignants de la classe, nous devons appliquer la méthode suivante CROSS JOIN .

Requête

SELECT T.TchrId,T.TeacherName,S.StudentName 
FROM #Teacher T
CROSS JOIN #Student S 

• SQL FIDDLE

Résultat

x--------------------------------------x
|  TchrId  | TeacherName | StudentName | 
x----------|-------------|-------------x            
|    T2    |     Jim     |    Vineeth  |
|    T2    |     Jim     |    Unni     |
|    T1    |     Mary    |    Vineeth  |
|    T1    |     Mary    |    Unni     |
x--------------------------------------x

Answer 2

La jointure croisée ne combine pas les lignes, si vous avez 100 lignes dans chaque table avec une correspondance 1 à 1, vous obtenez 10 000 résultats, la jointure interne ne renverra que 100 lignes dans la même situation.

Ces 2 exemples donneront le même résultat :

Jointure transversale

select * from table1 cross join table2 where table1.id = table2.fk_id

Jointure interne

select * from table1 join table2 on table1.id = table2.fk_id

Utilisez la dernière méthode

Answer 3

CROSS JOIN = (INNER) JOIN = virgule (",")

TL;DR La seule différence entre le CROSS JOIN SQL, le (INNER) JOIN et la virgule (",") (outre le fait que la virgule est moins prioritaire pour l'ordre d'évaluation) est que le (INNER) JOIN a un ON alors que le CROSS JOIN et la virgule n'en ont pas.

---

Re produits intermédiaires

Toutes trois produisent un produit relationnel "cartésien" intermédiaire conceptuel de type SQL, c'est-à-dire une jointure croisée, de toutes les combinaisons possibles d'une ligne de chaque table. Ce sont ON et/ou WHERE qui réduisent le nombre de lignes. Fiddle SQL

La norme SQL définit <comma> via product (7.5 1.b.ii), <cross join> alias CROSS JOIN via <comma> (7.7 1.a) et (INNER) JOIN ON <search condition> via <comma> plus WHERE (7.7 1.b).

Comme le dit Wikipedia :

Jointure transversale
CROSS JOIN renvoie le produit cartésien des lignes des tables de la jointure. En d'autres termes, elle produit des lignes qui combinent chaque ligne de la première table avec chaque ligne de la deuxième table.

Jointure interne
[...] Le résultat de la jointure peut être défini comme le résultat du produit cartésien (ou jointure croisée) de tous les enregistrements des tables (combinant chaque enregistrement de la table A avec chaque enregistrement de la table B), puis du renvoi de tous les enregistrements qui satisfont au prédicat de la jointure.

La "notation de jointure implicite" énumère simplement les tables à joindre, dans la clause FROM de l'instruction SELECT, en utilisant des virgules pour les séparer. Elle spécifie ainsi une jointure croisée

Re OUTER JOIN voir ma réponse Quelle est la différence entre "INNER JOIN" et "OUTER JOIN" ? .

Concernant les OUTER JOINs et l'utilisation de ON vs WHERE dans ceux-ci, voir ma réponse. Conditions dans LEFT JOIN (OUTER JOIN) par rapport à INNER JOIN .

Pourquoi comparer des colonnes entre tableaux ?

Lorsqu'il n'y a pas de lignes en double :

Chaque tableau contient les lignes qui constituent une déclaration vraie à partir d'un certain modèle de déclaration à remplir dans les blancs. (Il s'agit d'une affirmation vraie proposition de-- satisfait à -- un certain prédicat (caractéristique) .)

• Une table de base contient les lignes qui constituent une déclaration vraie à partir d'un modèle de déclaration donné par le DBA :

    /* rows where
    customer C.CustomerID has age C.Age and ...
    */
    FROM Customers C

• Le produit intermédiaire d'une jointure contient les lignes qui forment une déclaration vraie à partir du ET des modèles de ses opérandes :

    /* rows where
        customer C.CustomerID has age C.Age and ...
    AND movie M.Movie is rented by customer M.CustomerID and ...
    */
    FROM Customers C CROSS JOIN Movies M

• Les conditions ON et WHERE sont combinées par AND pour fournir un modèle supplémentaire. La valeur est à nouveau constituée des lignes qui satisfont à ce modèle :

    /* rows where
        customer C.CustomerID has age C.Age and ...
    AND movie M.Movie is rented by customer M.CustomerID and ...
    AND C.CustomerID = M.CustomerID
    AND C.Age >= M.[Minimum Age]
    AND C.Age = 18
    */
    FROM Customers C INNER JOIN Movies M
    ON C.CustomerID = M.CustomerID
    AND C.Age >= M.[Minimum Age]
    WHERE C.Age = 18

En particulier, la comparaison des colonnes pour l'égalité (SQL) entre les tables signifie que les lignes conservées du produit des parties du modèle des tables jointes ont la même valeur (non NULL) pour ces colonnes. C'est juste une coïncidence qu'un grand nombre de lignes soient typiquement supprimées par les comparaisons d'égalité entre les tables - ce qui est nécessaire et suffisant est de caractériser les lignes que vous voulez.

Il suffit d'écrire du SQL pour le modèle pour les lignes que vous voulez !

En ce qui concerne la signification des requêtes (et des tables par rapport aux conditions), voir :
Comment obtenir des données correspondantes d'une autre table SQL pour deux colonnes différentes : Jointure interne et/ou union ?
Existe-t-il une règle empirique pour construire une requête SQL à partir d'une description lisible par l'homme ?

Surcharge de la fonction "jointure croisée".

Malheureusement, le terme "jointure croisée" est utilisé pour.. :

• Le produit intermédiaire.
• CROSS JOIN.
• (INNER) JOIN avec un ON ou WHERE qui ne compare pas les colonnes d'une table à celles d'une autre. (Puisque cela a tendance à retourner un grand nombre de lignes de produits intermédiaires).

Ces différentes significations se confondent. (Par exemple, comme dans d'autres réponses et commentaires ici).

Utilisation de CROSS JOIN vs (INNER) JOIN vs virgule

La convention commune est la suivante :

• Utilisez le CROSS JOIN quand et seulement quand vous ne comparez pas les colonnes entre les tables. Cela permet de montrer que l'absence de comparaisons était intentionnelle.
• Utilisez (INNER) JOIN avec ON quand et seulement quand vous comparez des colonnes entre les tables. (Plus éventuellement d'autres conditions).
• N'utilisez pas de virgule.

Généralement, les conditions ne portant pas sur des paires de tables sont également conservées pour un WHERE. Mais elles peuvent être placées dans un (n INNER) JOIN ON pour obtenir les lignes appropriées pour l'argument d'un RIGHT, LEFT ou FULL (OUTER) JOIN.

Re "Ne pas utiliser de virgule" Mélanger la virgule avec le JOIN explicite peut induire en erreur car la virgule a une priorité inférieure. Mais étant donné le rôle du produit intermédiaire dans la signification de CROSS JOIN, (INNER) JOIN et virgule, les arguments en faveur de la convention ci-dessus consistant à ne pas l'utiliser du tout sont peu convaincants. Un CROSS JOIN ou une virgule est tout comme un (INNER) JOIN qui est sur une condition VRAIE. Un produit intermédiaire, ON et WHERE introduisent tous un AND dans le prédicat correspondant. Quelle que soit la manière dont on conçoit INNER JOIN ON - par exemple, en générant une ligne de sortie uniquement lorsqu'on trouve une paire de lignes d'entrée qui satisfait à la condition ON - elle renvoie néanmoins les lignes de la jointure croisée qui satisfont à la condition. La seule raison pour laquelle ON avait pour compléter la virgule en SQL était d'écrire OUTER JOINs. Bien sûr, une expression doit être claire, mais ce qui est clair dépend de ce que l'on entend par là.

Re Diagrammes de Venn Un diagramme de Venn avec deux cercles qui se croisent peut illustrer la différence entre lignes de sortie pour les INNER, LEFT, RIGHT & FULL JOINs pour la même entrée. Et lorsque ON est inconditionnellement VRAI, le résultat du INNER JOIN est le même que celui du CROSS JOIN. Il peut également illustrer le lignes d'entrée et de sortie pour INTERSECT, UNION & EXCEPT. Et lorsque les deux entrées ont les mêmes colonnes, le résultat de l'INTERSECT est le même que celui du NATURAL JOIN SQL standard, et le résultat de l'EXCEPT est le même que celui de certains idiomes impliquant LEFT & RIGHT JOIN. Mais il est vrai que pas illustrer comment le (INNER) JOIN fonctionne en général. C'est juste semble plausible à première vue . Il peut identifier pièces d'entrée et/ou de sortie pour cas particuliers de ON, PKs (clés primaires), FKs (clés étrangères) et/ou SELECT. Pour s'en rendre compte, il suffit d'identifier Quels sont exactement les éléments des ensembles représentés par les cercles ? . (Ce que les présentations confuses ne rendent jamais clair.) Rappelez-vous qu'en général pour les jointures les lignes de sortie ont des titres différents des lignes d'entrée . Et les tables SQL sont sacs pas fixe de rangs avec des NULLs .

Answer 4

Jointure interne

La jointure qui affiche uniquement les lignes qui ont une correspondance dans les deux tables jointes est connue sous le nom de jointure interne. Il s'agit de la jointure par défaut dans le Designer de requêtes et de vues.

Syntaxe de la jointure interne

SELECT t1.column_name,t2.column_name
FROM table_name1 t1
INNER JOIN table_name2 t2
ON t1.column_name=t2.column_name

Jointure transversale

Une jointure croisée qui produit le produit cartésien des tables impliquées dans la jointure. La taille d'un produit cartésien est le nombre de lignes de la première table multiplié par le nombre de lignes de la deuxième table.

Syntaxe de la jointure croisée

SELECT * FROM table_name1
CROSS JOIN table_name2

Ou nous pouvons aussi l'écrire d'une autre manière

SELECT * FROM table_name1,table_name2

Maintenant, vérifiez la requête ci-dessous pour la jointure croisée

Exemple

SELECT * FROM UserDetails
CROSS JOIN OrderDetails

Ou

SELECT * FROM UserDetails, OrderDetails

Answer 5

CROSS JOIN

Le CROSS JOIN est destiné à générer un produit cartésien.

Un produit cartésien prend deux ensembles A et B et génère toutes les permutations possibles d'enregistrements de paires à partir de deux ensembles de données donnés.

Par exemple, supposons que vous ayez les éléments suivants ranks y suits les tables de la base de données :

Et le ranks a les rangs suivants :

| name  | symbol | rank_value |
|-------|--------|------------|
| Ace   | A      | 14         |
| King  | K      | 13         |
| Queen | Q      | 12         |
| Jack  | J      | 11         |
| Ten   | 10     | 10         |
| Nine  | 9      |  9         |

Alors que le suits contient les enregistrements suivants :

| name    | symbol |
|---------|--------|
| Club    |       |
| Diamond |       |
| Heart   |       |
| Spade   |       |

En tant que requête CROSS JOIN comme celle qui suit :

SELECT
   r.symbol AS card_rank,
   s.symbol AS card_suit
FROM
   ranks r
CROSS JOIN
   suits s

générera toutes les permutations possibles de ranks y suites paires :

| card_rank | card_suit |
|-----------|-----------|
| A         |          |
| A         |          |
| A         |          |
| A         |          |
| K         |          |
| K         |          |
| K         |          |
| K         |          |
| Q         |          |
| Q         |          |
| Q         |          |
| Q         |          |
| J         |          |
| J         |          |
| J         |          |
| J         |          |
| 10        |          |
| 10        |          |
| 10        |          |
| 10        |          |
| 9         |          |
| 9         |          |
| 9         |          |
| 9         |          |

INNER JOIN

En revanche, INNER JOIN ne renvoie pas le produit cartésien des deux ensembles de données qui se rejoignent.

Au lieu de cela, le INNER JOIN prend tous les éléments de la table de gauche et les compare aux enregistrements de la table de droite de sorte que.. :

• si aucun enregistrement ne correspond dans la table de droite, la ligne de gauche est éliminée par filtrage de l'ensemble de résultats
• pour tout enregistrement correspondant dans la table de droite, la ligne de gauche est répétée comme s'il existait un produit cartésien entre cet enregistrement et tous ses enregistrements enfants associés dans la table de droite.

Par exemple, supposons que nous ayons une relation de type "one-to-many table" entre un parent post et un enfant post_comment qui se présentent comme suit :

Maintenant, si le post La table a les enregistrements suivants :

| id | title     |
|----|-----------|
| 1  | Java      |
| 2  | Hibernate |
| 3  | JPA       |

et le post_comments a ces lignes :

| id | review    | post_id |
|----|-----------|---------|
| 1  | Good      | 1       |
| 2  | Excellent | 1       |
| 3  | Awesome   | 2       |

Une requête INNER JOIN comme celle qui suit :

SELECT
   p.id AS post_id,
   p.title AS post_title,
   pc.review  AS review
FROM post p
INNER JOIN post_comment pc ON pc.post_id = p.id

va inclure tous les post ainsi que tous les enregistrements associés post_comments :

| post_id | post_title | review    |
|---------|------------|-----------|
| 1       | Java       | Good      |
| 1       | Java       | Excellent |
| 2       | Hibernate  | Awesome   |

En gros, vous pouvez penser à la INNER JOIN comme un CROSS JOIN filtré où seuls les enregistrements correspondants sont conservés dans le jeu de résultats final.