Question INNER JOIN ON vs clause WHERE


Pour simplifier, supposons que tous les champs pertinents NOT NULL.

Tu peux faire:

SELECT
    table1.this, table2.that, table2.somethingelse
FROM
    table1, table2
WHERE
    table1.foreignkey = table2.primarykey
    AND (some other conditions)

Ou sinon:

SELECT
    table1.this, table2.that, table2.somethingelse
FROM
    table1 INNER JOIN table2
    ON table1.foreignkey = table2.primarykey
WHERE
    (some other conditions)

Est-ce que ces deux fonctionnent de la même manière MySQL?


761
2018-06-19 16:16


origine


Réponses:


INNER JOIN est la syntaxe ANSI que vous devez utiliser.

Il est généralement considéré plus lisible, en particulier lorsque vous joignez beaucoup de tables.

Il peut également être facilement remplacé par un OUTER JOIN chaque fois qu'un besoin se présente.

le WHERE La syntaxe est plus orientée vers le modèle relationnel.

Un résultat de deux tables JOINed est un produit cartésien des tables auxquelles un filtre est appliqué et qui ne sélectionne que les lignes dont les colonnes de jonction correspondent.

C'est plus facile de voir ça avec WHERE syntaxe.

Comme pour votre exemple, en MySQL (et en SQL en général) ces deux requêtes sont synonymes.

Notez également que MySQL a également un STRAIGHT_JOIN clause.

En utilisant cette clause, vous pouvez contrôler JOIN order: quelle table est scannée dans la boucle externe et laquelle est dans la boucle interne.

Vous ne pouvez pas contrôler cela dans MySQL en utilisant WHERE syntaxe.


611
2018-06-19 16:17



D'autres ont souligné que INNER JOIN aide la lisibilité humaine, et c'est une priorité absolue; Je suis d'accord. Laissez-moi essayer d'expliquer Pourquoi la syntaxe de jointure est plus lisible.

Une requête SELECT de base est la suivante:

SELECT stuff
FROM tables
WHERE conditions

La clause SELECT nous dit quelle nous revenons; la clause FROM nous dit  nous l'obtenons, et la clause WHERE nous dit lequel ceux que nous obtenons.

JOIN est une déclaration sur les tables, comment elles sont liées ensemble (conceptuellement, en fait, dans une seule table). Tous les éléments de requête qui contrôlent les tables - d'où nous venons - proviennent sémantiquement de la clause FROM (et bien sûr, c'est là que les éléments JOIN vont). Mettre des éléments de jonction dans la clause WHERE confond le lequel et le d'où; C'est pourquoi la syntaxe JOIN est préférée.


145
2018-06-19 16:30



 Appliquer des instructions conditionnelles dans ON / WHERE

Ici, j'ai expliqué à propos des étapes de traitement de requêtes logiques.


Référence: Interrogation T-SQL dans Microsoft SQL Server 2005
Éditeur: Microsoft Press
Date du Pub: 07 Mars 2006
Imprimer ISBN-10: 0-7356-2313-9
Imprimer ISBN-13: 978-0-7356-2313-2
Pages: 640

Dans l'interrogation T-SQL de Microsoft SQL Server 2005

(8)  SELECT (9) DISTINCT (11) TOP <top_specification> <select_list>
(1)  FROM <left_table>
(3)       <join_type> JOIN <right_table>
(2)       ON <join_condition>
(4)  WHERE <where_condition>
(5)  GROUP BY <group_by_list>
(6)  WITH {CUBE | ROLLUP}
(7)  HAVING <having_condition>
(10) ORDER BY <order_by_list>

Le premier aspect notable de SQL qui est différent des autres langages de programmation est l'ordre dans lequel le code est traité. Dans la plupart des langages de programmation, le code est traité dans l'ordre dans lequel il est écrit. En SQL, la première clause traitée est la clause FROM, tandis que la clause SELECT, qui apparaît en premier, est traitée presque en dernier.

Chaque étape génère une table virtuelle utilisée comme entrée pour l'étape suivante. Ces tables virtuelles ne sont pas disponibles pour l'appelant (application cliente ou requête externe). Seule la table générée par l'étape finale est renvoyée à l'appelant. Si une certaine clause n'est pas spécifiée dans une requête, l'étape correspondante est simplement ignorée.

Brève description des phases de traitement de requêtes logiques

Ne vous inquiétez pas trop si la description des étapes ne semble pas avoir beaucoup de sens pour l'instant. Ceux-ci sont fournis comme référence. Les sections qui suivent l'exemple du scénario couvriront les étapes de manière beaucoup plus détaillée.

  1. FROM: un produit cartésien (jointure croisée) est exécuté entre les deux premières tables de la clause FROM et, par conséquent, la table virtuelle VT1 est générée.

  2. ON: Le filtre ON est appliqué à VT1. Seules les lignes pour lesquelles le <join_condition> TRUE est inséré dans VT2.

  3. OUTER (join): Si un OUTER JOIN est spécifié (par opposition à un CROSS JOIN ou un INNER JOIN), les lignes de la ou des tables préservées pour lesquelles une correspondance n'a pas été trouvée sont ajoutées aux lignes de VT2 en tant que lignes externes, générant VT3. Si plus de deux tables apparaissent dans la clause FROM, les étapes 1 à 3 sont appliquées à plusieurs reprises entre le résultat de la dernière jointure et la table suivante dans la clause FROM jusqu'à ce que toutes les tables soient traitées.

  4. OERE: Le filtre WHERE est appliqué à VT3. Seules les lignes pour lesquelles le <where_condition> TRUE est inséré dans VT4.

  5. GROUP BY: Les lignes de VT4 sont organisées en groupes en fonction de la liste de colonnes spécifiée dans la clause GROUP BY. VT5 est généré.

  6. CUBE | ROLLUP: Des supergroupes (groupes de groupes) sont ajoutés aux lignes à partir de VT5, générant VT6.

  7. HAVING: Le filtre HAVING est appliqué à VT6. Uniquement les groupes pour lesquels <having_condition> TRUE est inséré dans VT7.

  8. SELECT: La liste SELECT est traitée, générant VT8.

  9. DISTINCT: les lignes en double sont supprimées de VT8. VT9 est généré.

  10. ORDER BY: Les lignes de VT9 sont triées en fonction de la liste de colonnes spécifiée dans la clause ORDER BY. Un curseur est généré (VC10).

  11. TOP: Le nombre ou le pourcentage de lignes spécifié est sélectionné depuis le début de VC10. La table VT11 est générée et renvoyée à l'appelant.



     Par conséquent, (INNER JOIN) ON va filtrer les données (le nombre de données de VT sera réduit ici lui-même) avant d'appliquer la clause WHERE. Les conditions de jointure suivantes seront exécutées avec des données filtrées qui améliorent les performances. Après cela, seule la condition WHERE appliquera les conditions de filtre.

(L'application d'instructions conditionnelles dans ON / WHERE ne fera pas beaucoup de différence dans quelques cas, cela dépend du nombre de tables que vous avez joint et du nombre de lignes disponibles dans chaque table de jointure)


112
2017-12-22 06:24



La syntaxe de la jointure ANSI implicite est plus ancienne, moins évidente et n'est pas recommandée.

De plus, l'algèbre relationnelle permet l'interchangeabilité des prédicats WHERE clause et la INNER JOIN, même INNER JOIN requêtes avec WHERE Les clauses peuvent avoir les prédicats réarrangés par l'optimiseur.

Je vous recommande d'écrire les requêtes de la manière la plus facile possible.

Parfois, cela comprend la réalisation de INNER JOIN relativement "incomplet" et en mettant certains des critères dans le WHERE simplement pour rendre les listes de critères de filtrage plus facilement maintenables.

Par exemple, au lieu de:

SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
    ON ca.CustomerID = c.CustomerID
    AND c.State = 'NY'
INNER JOIN Accounts a
    ON ca.AccountID = a.AccountID
    AND a.Status = 1

Écrire:

SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
    ON ca.CustomerID = c.CustomerID
INNER JOIN Accounts a
    ON ca.AccountID = a.AccountID
WHERE c.State = 'NY'
    AND a.Status = 1

Mais cela dépend, bien sûr.


55
2018-06-19 16:23



Les jointures implicites (qui sont ce que votre première requête est connue) deviennent beaucoup plus confuses, difficiles à lire et difficiles à maintenir une fois que vous avez besoin d'ajouter plus de tables à votre requête. Imaginez faire la même requête et le même type de jointure sur quatre ou cinq tables différentes ... c'est un cauchemar.

L'utilisation d'une jointure explicite (votre deuxième exemple) est beaucoup plus lisible et facile à maintenir.


25
2018-06-19 16:19



Je ferai également remarquer que l'utilisation de l'ancienne syntaxe est plus sujette à erreur. Si vous utilisez des jointures internes sans clause ON, vous obtiendrez une erreur de syntaxe. Si vous utilisez l'ancienne syntaxe et oubliez l'une des conditions de jointure dans la clause where, vous obtiendrez une jointure croisée. Les développeurs corrigent souvent cela en ajoutant le mot-clé distinct (plutôt que de réparer la jointure parce qu'ils ne réalisent toujours pas que la jointure elle-même est cassée), ce qui peut sembler résoudre le problème, mais ralentira considérablement la requête.

De plus, pour la maintenance, si vous avez une jointure croisée dans l'ancienne syntaxe, comment le mainteneur saura-t-il si vous voulez en avoir un (il y a des situations où des jointures croisées sont nécessaires) ou s'il s'agit d'un accident?

Permettez-moi de vous diriger vers cette question pour voir pourquoi la syntaxe implicite est mauvaise si vous utilisez des jointures à gauche. Sybase * = à Ansi Standard avec 2 tables externes différentes pour la même table interne

Plus (la diatribe personnelle ici), la norme utilisant les jointures explicites a plus de 20 ans, ce qui signifie que la syntaxe de jointure implicite est obsolète depuis 20 ans. Souhaitez-vous écrire le code de l'application en utilisant une syntaxe qui a été obsolète pendant 20 ans? Pourquoi voulez-vous écrire le code de base de données qui est?


21
2018-06-19 16:46



Ils ont une signification humaine différente.

Toutefois, en fonction de l'optimiseur de requête, ils peuvent avoir la même signification pour la machine.

Vous devriez toujours coder pour être lisible.

C'est-à-dire, s'il s'agit d'une relation intégrée, utilisez la jointure explicite. Si vous faites correspondre des données faiblement liées, utilisez la clause where.


12
2018-06-19 16:20



Le standard SQL: 2003 a modifié certaines règles de précédence, de sorte qu'une instruction JOIN a la priorité sur une jointure "virgule". Cela peut réellement changer les résultats de votre requête en fonction de la façon dont il est configuré. Cela provoque des problèmes pour certaines personnes lorsque MySQL 5.0.12 est passé à la norme.

Ainsi, dans votre exemple, vos requêtes fonctionneraient de la même manière. Mais si vous avez ajouté une troisième table: SELECT ... FROM table1, table2 JOIN tableau3 ON ... O WH ...

Avant MySQL 5.0.12, table1 et table2 seraient d'abord jointes, puis table3. Maintenant (5.0.12 et sur), table2 et table3 sont jointes d'abord, puis table1. Cela ne change pas toujours les résultats, mais cela peut et vous ne pouvez même pas le réaliser.

Je n'utilise plus jamais la syntaxe "virgule", en optant pour votre deuxième exemple. C'est beaucoup plus lisible quand même, les conditions JOIN sont avec les JOINs, pas séparées dans une section de requête séparée.


10
2018-06-19 17:28



Je sais que vous parlez de MySQL, mais de toute façon: Dans Oracle 9, les jointures explicites et les jointures implicites génèrent des plans d'exécution différents. AFAIK qui a été résolu dans Oracle 10+: il n'y a plus de différence.


4
2018-06-19 17:03



La syntaxe de jointure ANSI est définitivement plus portable.

Je passe par une mise à niveau de Microsoft SQL Server, et je mentionnerais également que la syntaxe = * et * = pour les jointures externes dans SQL Server n'est pas supportée (sans mode de compatibilité) pour le serveur sql 2005 et plus tard.


1
2018-06-19 16:50