Question Jointure SQL: clause clause vs. clause


Après l'avoir lu, c'est ne pas un duplicata de Jointures SQL explicites vs implicites. La réponse peut être liée (ou même la même) mais le question est différent.


Quelle est la différence et que devrait-il y avoir dans chacun?

Si je comprends bien la théorie, l'optimiseur de requêtes devrait pouvoir utiliser les deux de manière interchangeable.


495
2017-12-09 20:14


origine


Réponses:


Ce n'est pas la même chose.

Considérez ces requêtes:

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
WHERE Orders.ID = 12345

et

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID 
    AND Orders.ID = 12345

Le premier renverra une commande et ses lignes, le cas échéant, pour le numéro de commande 12345. La seconde renverra toutes les commandes, mais seulement la commande 12345 aura des lignes associées.

Avec un INNER JOIN, les clauses sont efficacement équivalent. Cependant, le fait qu’elles soient fonctionnellement identiques, en ce sens qu’elles produisent les mêmes résultats, ne signifie pas que les deux types de clauses ont la même signification sémantique.


656
2017-12-09 20:21



  • Peu importe pour les jointures internes
  • Questions pour les jointures externes

    une. WHERE clause: Après joindre. Les enregistrements seront filtrés après que la jointure ait eu lieu.

    b. ON clause - Avant joindre. Les enregistrements (de la table de droite) seront filtrés avant de rejoindre. Cela peut finir par être nul dans le résultat (depuis la jointure externe).



Exemple: Considérez les tableaux ci-dessous:

    1. documents:
     | id    | name        |
     --------|-------------|
     | 1     | Document1   |
     | 2     | Document2   |
     | 3     | Document3   |
     | 4     | Document4   |
     | 5     | Document5   |


    2. downloads:
     | id   | document_id   | username |
     |------|---------------|----------|
     | 1    | 1             | sandeep  |
     | 2    | 1             | simi     |
     | 3    | 2             | sandeep  |
     | 4    | 2             | reya     |
     | 5    | 3             | simi     |

a) À l'intérieur WHERE clause:

  SELECT documents.name, downloads.id
    FROM documents
    LEFT OUTER JOIN downloads
      ON documents.id = downloads.document_id
    WHERE username = 'sandeep'

 For above query the intermediate join table will look like this.

    | id(from documents) | name         | id (from downloads) | document_id | username |
    |--------------------|--------------|---------------------|-------------|----------|
    | 1                  | Document1    | 1                   | 1           | sandeep  |
    | 1                  | Document1    | 2                   | 1           | simi     |
    | 2                  | Document2    | 3                   | 2           | sandeep  |
    | 2                  | Document2    | 4                   | 2           | reya     |
    | 3                  | Document3    | 5                   | 3           | simi     |
    | 4                  | Document4    | NULL                | NULL        | NULL     |
    | 5                  | Document5    | NULL                | NULL        | NULL     |

  After applying the `WHERE` clause and selecting the listed attributes, the result will be: 

   | name         | id |
   |--------------|----|
   | Document1    | 1  |
   | Document2    | 3  | 

b) à l'intérieur JOIN clause

  SELECT documents.name, downloads.id
  FROM documents
    LEFT OUTER JOIN downloads
      ON documents.id = downloads.document_id
        AND username = 'sandeep'

For above query the intermediate join table will look like this.

    | id(from documents) | name         | id (from downloads) | document_id | username |
    |--------------------|--------------|---------------------|-------------|----------|
    | 1                  | Document1    | 1                   | 1           | sandeep  |
    | 2                  | Document2    | 3                   | 2           | sandeep  |
    | 3                  | Document3    | NULL                | NULL        | NULL     |
    | 4                  | Document4    | NULL                | NULL        | NULL     |
    | 5                  | Document5    | NULL                | NULL        | NULL     |

Notice how the rows in `documents` that did not match both the conditions are populated with `NULL` values.

After Selecting the listed attributes, the result will be: 

   | name       | id   |
   |------------|------|
   |  Document1 | 1    |
   |  Document2 | 3    | 
   |  Document3 | NULL |
   |  Document4 | NULL | 
   |  Document5 | NULL | 

166
2018-01-07 20:54



Sur INNER JOINs ils sont interchangeables, et l'optimiseur les réorganisera à volonté.

Sur OUTER JOINs, ils ne sont pas nécessairement interchangeables, selon le côté de la jointure dont ils dépendent.

Je les ai mis dans n'importe quel endroit en fonction de la lisibilité.


137
2017-12-09 20:21



La façon dont je le fais est:

Toujours mettre les conditions de jointure dans la clause on Si vous faites une jointure interne, n’ajoutez aucune condition where à la clause on, placez-les dans la clause where

Si vous effectuez une jointure à gauche, ajoutez des conditions where à la clause on pour la table située à droite de la jointure. Ceci est indispensable car l'ajout d'une clause where qui référence le côté droit de la jointure convertira la jointure en jointure interne (à une exception près décrite ci-dessous).

L'exception est que lorsque vous recherchez les enregistrements qui ne sont pas dans une table particulière, vous ajouteriez la référence à un identifiant unique (qui n'est jamais nul) dans la table de jointure droite à la clause where de cette façon "Où t2. idfield est nul ". Donc, la seule fois où vous devez référencer une table sur le côté droit de la jointure, c'est pour trouver les enregistrements qui ne sont pas dans la table.


32
2017-12-09 20:57



Sur une jointure interne, ils signifient la même chose. Cependant, vous obtiendrez des résultats différents dans une jointure externe en fonction de la condition de jointure dans la clause WHERE vs ON. Jeter un coup d'œil à cette question connexe et cette réponse (par moi).

Je pense qu'il est plus logique d'avoir toujours l'habitude de placer la condition de jointure dans la clause ON (à moins qu'il s'agisse d'une jointure externe et que vous le vouliez dans la clause where) car cela clarifie la lecture de votre requête dans quelles conditions les tables sont jointes, et cela permet également d'éviter que la clause WHERE ne soit composée de dizaines de lignes de long.


29
2017-12-09 20:20



Cet article explique clairement la différence. Il explique également "ON join_condition vs WHERE assemblé_condition ou join_alias est nul".

La clause WHERE filtre le côté gauche et le côté droit du JOIN, tandis que la clause ON filtre toujours le côté droit uniquement.

  1. Si vous voulez toujours récupérer les lignes du côté gauche et ne rejoindre JOIN que si certaines conditions correspondent, alors vous devriez utiliser la clause ON.
  2. Si vous souhaitez filtrer le produit de la jonction des deux côtés, vous devez utiliser la clause WHERE.

13
2018-05-25 14:20



En termes d'optimiseur, cela ne devrait pas faire de différence si vous définissez vos clauses de jointure avec ON ou WHERE.

Cependant, à mon humble avis, je pense qu'il est beaucoup plus clair d'utiliser la clause ON lors de l'exécution de jointures. De cette façon, vous avez une section spécifique de votre requête qui dicte comment la jointure est gérée par rapport au reste des clauses WHERE.


7
2017-12-09 20:21



Il y a une grande différence entre où clause contre. sur la clause, quand il s'agit de rejoindre à gauche.

Voici un exemple:

mysql> desc t1; 
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| fid   | int(11)     | NO   |     | NULL    |       |
| v     | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

Il y a id de la table t2.

mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| v     | varchar(10) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Requête sur "on clause": 

mysql> SELECT * FROM `t1` left join t2 on fid = t2.id AND t1.v = 'K' 
    -> ;
+----+-----+---+------+------+
| id | fid | v | id   | v    |
+----+-----+---+------+------+
|  1 |   1 | H | NULL | NULL |
|  2 |   1 | B | NULL | NULL |
|  3 |   2 | H | NULL | NULL |
|  4 |   7 | K | NULL | NULL |
|  5 |   5 | L | NULL | NULL |
+----+-----+---+------+------+
5 rows in set (0.00 sec)

Requête sur "where clause":

mysql> SELECT * FROM `t1` left join t2 on fid = t2.id where t1.v = 'K';
+----+-----+---+------+------+
| id | fid | v | id   | v    |
+----+-----+---+------+------+
|  4 |   7 | K | NULL | NULL |
+----+-----+---+------+------+
1 row in set (0.00 sec)

Il est clair que, la première requête renvoie un enregistrement de t1 et sa ligne dépendante de t2, le cas échéant, pour la ligne t1.v = 'K'.

La seconde requête retourne des lignes de t1, mais seulement pour t1.v = 'K' aura une ligne associée avec elle.


5
2018-03-13 06:31



pour de meilleures performances, les tables doivent avoir une colonne indexée spéciale à utiliser pour JOINS.

Donc, si la colonne sur laquelle vous conditionnez n’est pas l’une de ces colonnes indexées, je pense qu’il est préférable de la conserver dans WHERE.

donc vous JOINTEz en utilisant les colonnes indexées, puis après JOIN vous exécutez la condition sur la colonne non indexée.


1
2017-12-12 21:10



Normalement, le filtrage est traité dans la clause WHERE une fois que les deux tables ont déjà été jointes. Il est possible, bien que vous souhaitiez filtrer une ou les deux tables avant de les rejoindre. c'est-à-dire que la clause where s'applique à l'ensemble des résultats alors que la clause on s'applique uniquement à la jointure en question.


1
2018-02-16 05:29



Je pense que c'est l'effet de séquence de jointure. Dans le cas de jointure supérieur gauche, SQL do Left se joint d'abord, puis effectue le filtrage. Dans le cas inférieur, recherchez d'abord Orders.ID = 12345, puis faites-le.


0
2018-01-07 03:49