Joint intérieur sur vs où clause

par souci de simplicité, supposons que tous les champs pertinents sont NOT NULL .

Vous pouvez le faire:

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

ou bien:

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

ces deux fonctionnent sur le même chemin, dans MySQL ?

778
demandé sur Uwe Keim 2009-06-19 20:16:24
la source

10 ответов

INNER JOIN est une syntaxe ANSI que vous devez utiliser.

il est généralement considéré plus lisible, surtout quand vous rejoignez beaucoup de tableaux.

il peut aussi être facilement remplacé par un OUTER JOIN en cas de besoin.

la syntaxe WHERE est davantage axée sur le modèle relationnel.

un résultat de deux tables JOIN ed est un produit cartésien des tables auxquelles un filtre est appliqué qui ne sélectionne que les lignes avec les colonnes correspondantes.

c'est plus facile à voir avec la syntaxe WHERE .

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

notez aussi que MySQL a aussi une clause STRAIGHT_JOIN .

en utilisant cette clause, vous pouvez contrôler l'ordre JOIN : quelle table est scannée dans la boucle extérieure et laquelle est dans la boucle intérieure boucle.

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

622
répondu Quassnoi 2017-11-19 19:28:17
la source

D'autres ont souligné que la jonction intérieure 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 de sélection de base est celle-ci:

SELECT stuff
FROM tables
WHERE conditions

La clause SELECT nous dit ce nous sommes de retour; la clause from nous dit nous sommes en train de partir, et la clause where nous dit que nous obtenons.

joindre est une déclaration sur les tables, comment ils sont liés ensemble (conceptuellement, en fait, dans un seul tableau). Tout élément de requête qui contrôle les tables - où nous obtenons des choses - appartient sémantiquement à la clause de FROM (et bien sûr, C'est là que vont les éléments de jointure). La mise des éléments de jointure dans la clause où se confond les qui et les où-de ; c'est pourquoi la syntaxe JOIN est préférée.

149
répondu Carl Manaster 2009-06-19 20:30:59
la source

application des mentions conditionnelles dans ON / WHERE

ici, j'ai expliqué au sujet des étapes de traitement de requête logique.


référence: Inside Microsoft® SQL Server™ 2005 T-SQL Querying

Editeur: Microsoft Press

Date De Publication: 7 Mars 2006

Print ISBN-10: 0-7356-2313-9

Imprimer ISBN-13: 978-0-7356-2313-2

Pages prix en ECU: 640

Inside Microsoft ® SQL Server™ 2005 T-SQL Querying

(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 où il est écrit. En SQL, la première clause qui est la clause processed 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 qui est utilisée comme entrée pour l'étape suivante. Ces tables virtuelles ne sont pas disponibles pour l'appelant (application client ou requête externe). Seul le tableau généré par la dernière étape est retourné à l'appelant. Si une clause n'est pas spécifié dans une requête, l'étape correspondante est simplement ignorée.

mémoire Description de la Logique de Traitement de Requête Phases

Ne vous inquiétez pas trop si la description des étapes ne semble pas faire beaucoup de sens pour l'instant. Ces sont fournies à titre de référence. Les Sections qui suivent l'exemple de scénario couvriront les étapes de façon beaucoup plus détaillée.

  1. FROM: un produit cartésien (Cross jointure) est effectué entre les deux premières tables de la clause FROM, et en conséquence, 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> est vrai sont insérées dans VT2.

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

  4. où: le filtre où est appliqué à VT3. Seules les lignes pour lesquelles le <where_condition> est vrai sont insérées dans VT4.

  5. GROUP BY: les lignes de VT4 sont organisées en groupes basés sur la liste des colonnes spécifiée dans le groupe par clause. VT5 est généré.

  6. CUBE/ ROLLUP: les supergroupes (groupes de groupes) sont ajoutés aux lignes de VT5, générant VT6.

  7. ayant: le filtre ayant est appliqué à VT6. Seuls les groupes pour lesquels le <having_condition> est vrai sont insérés dans VT7.

  8. sélectionner: la liste de sélection est traitée, générant VT8.

  9. DISTINCT: les lignes dupliquées sont retiré de VT8. VT9 est généré.

  10. ordre par: les lignes de VT9 sont triées selon la liste des colonnes spécifiée dans l'ordre Par clause. Un curseur est généré (VC10).

  11. haut: le nombre ou le pourcentage déterminé de lignes est choisi à partir du début de VC10. Le tableau VT11 est généré et retourné à l'appelant.




Par conséquent, (INNER JOIN) ON va filtrer les données (le nombre de données de VT sera réduit ici 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 seulement la condition où s'appliquera des conditions de filtre.

(L'application d'énoncés conditionnels dans ON / WHERE ne fera pas beaucoup de différence dans quelques cas. Cela dépend du nombre de tables que vous avez rejoint et nombre de lignes disponibles dans chaque tableau de jointure)

115
répondu rafidheen 2015-09-28 21:38:37
la source

la syntaxe implicite de join ANSI est plus ancienne, moins évidente et non recommandée.

de plus, l'algèbre relationnelle permet l'interchangeabilité des prédicats dans la clause WHERE et le INNER JOIN , de sorte que même les requêtes INNER JOIN avec les clauses WHERE 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 lisible possible.

parfois ce inclut le fait de rendre le INNER JOIN relativement" incomplet "et de mettre certains des critères dans le WHERE simplement pour rendre les listes de critères de filtrage plus faciles à maintenir.

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
répondu Cade Roux 2009-06-19 20:23:45
la source

jointures implicites (qui est ce que votre première requête est connue sous le nom) deviennent beaucoup plus confus, difficile à lire, et difficile à maintenir une fois que vous avez besoin de commencer à ajouter plus de tables à votre requête. Imaginez faire cette même requête et le type de joindre sur quatre ou cinq tables différentes ... c'est un cauchemar.

utiliser une jointure explicite (votre deuxième exemple) est beaucoup plus lisible et facile à maintenir.

26
répondu matt b 2009-06-19 20:19:14
la source

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

en outre pour la maintenance si vous avez une jointure croisée dans l'ancienne syntaxe, comment le responsable saura-t-il si vous vouliez en avoir une (il y a des situations où des jointures croisées sont nécessaires) ou si c'était un accident qui devrait être corrigé?

Permettez-moi de vous montrer cette question pour voir pourquoi la syntaxe implicite est mauvaise si vous utilisez jointures gauche. Sybase * = norme Ansi avec 2 tables extérieures différentes pour une même table intérieure

Plus (rant personnel ici), la norme utilisant les jointures explicites a plus de 20 ans, ce qui signifie que la syntaxe implicite de jointure a été dépassée pour ces 20 ans. Écririez-vous le code de l'application en utilisant la syntaxe qui est périmée depuis 20 ans? Pourquoi voulez-vous écrire le code de la base de données qui est?

22
répondu HLGEM 2017-05-23 15:02:50
la source

ils ont une signification humaine différente.

Toutefois, selon l'optimiseur de requête, ils peuvent avoir le même sens pour la machine.

vous devez toujours coder pour être lisible.

C'est-à-dire, s'il s'agit d'une relation intégrée, utiliser la jointure explicite. si vous utilisez des données faiblement apparentées, utilisez la clause "où".

12
répondu John Gietzen 2010-04-16 02:04:49
la source

la norme SQL:2003 a changé certaines règles de préséance de sorte qu'une déclaration de jointure a priorité sur une jointure" virgule". Cela peut en fait changer les résultats de votre requête en fonction de la façon dont elle est configurée. Cela cause des problèmes pour certaines personnes lorsque MySQL 5.0.12 est passé à l'adhésion à la norme.

donc dans votre exemple, vos requêtes fonctionneraient de la même manière. Mais si vous avez ajouté un troisième tableau: SÉLECTIONNER. .. À partir de la table 1, La Table 2 rejoint la table 3 ... OÙ. ..

avant MySQL 5.0.12, les tableaux 1 et 2 seraient joints en premier, puis le tableau 3. Maintenant (5.0.12 et on), les tableaux 2 et 3 sont joints en premier, puis le tableau 1. Il ne change pas toujours les résultats, mais il peut et vous ne pouvez même pas le réaliser.

Je n'utilise plus la syntaxe" virgule", optant pour votre second exemple. C'est beaucoup plus lisible de toute façon, les conditions de jointure sont avec les jointures, pas séparées dans une section de requête séparée.

10
répondu Brent Baisley 2009-06-19 21:28:56
la source

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

4
répondu João Marcus 2009-06-19 21:03:42
la source

la syntaxe de jointure ANSI est certainement plus portable.

je passe par une mise à niveau de Microsoft SQL Server, et je voudrais également mentionner que la syntaxe =* et *= pour les jointures externes dans SQL Server N'est pas prise en charge (sans mode de compatibilité) pour sql server 2005 et plus tard.

1
répondu Benzo 2009-06-19 20:50:12
la source

Autres questions sur sql mysql join inner-join