SQL LEFT join vs plusieurs tables Sur FROM line?
la plupart des dialectes SQL acceptent les deux requêtes suivantes:
SELECT a.foo, b.foo
FROM a, b
WHERE a.x = b.x
SELECT a.foo, b.foo
FROM a
LEFT JOIN b ON a.x = b.x
Maintenant, évidemment, quand vous avez besoin d'une jointure externe, la seconde syntaxe est requise. Mais en faisant une jointure interne pourquoi devrais-je préfère la deuxième syntaxe à la première (ou vice versa)?
11 réponses
l'ancienne syntaxe, avec la simple énumération des tables, et l'utilisation de la clause WHERE pour spécifier les critères de jointure, est dépréciée dans la plupart des bases de données modernes.
ce n'est pas seulement pour montrer, l'ancienne syntaxe a la possibilité d'être ambiguë quand vous utilisez les jointures internes et externes dans la même requête.
Laissez-moi vous donner un exemple.
supposons que vous ayez 3 tables dans votre système:
Company
Department
Employee
chaque tableau contient de nombreuses lignes, liées entre elles. Vous avez plusieurs sociétés, et chaque entreprise peut avoir plusieurs ministères, et chaque ministère peut avoir plusieurs employés.
Ok, donc maintenant vous voulez faire ce qui suit:
répertorie toutes les entreprises, et comprend tous leurs départements, et tous leurs employés. Notez que certaines entreprises n'ont pas de ministères, mais assurez-vous d'inclure aussi bien. Assurez-vous que vous récupérez seulement les départements qui ont des employés, mais toujours énumérer toutes les entreprises.
alors vous faites ceci:
SELECT * -- for simplicity
FROM Company, Department, Employee
WHERE Company.ID *= Department.CompanyID
AND Department.ID = Employee.DepartmentID
Notez que la dernière est une jointure interne, afin de remplir les critères que vous voulez seulement les ministères avec les gens.
Ok, alors ce qui se passe maintenant. Le problème est que cela dépend du moteur de la base de données, de l'optimiseur de requêtes, des index et des statistiques de table. Laissez-moi vous expliquer.
si l'optimiseur de requête détermine que la façon de faire cela est de prendre d'abord une entreprise, puis trouver les départements, et ensuite faire une réunion interne avec les employés, vous n'allez pas obtenir des entreprises qui n'ont pas de départements.
la raison en est que la clause WHERE détermine quelles lignes finissent dans le résultat final, pas des parties individuelles des lignes.
et dans ce cas, en raison de la gauche rejoindre, le Département.La colonne ID sera nulle, et donc quand il s'agit de la jointure interne à L'employé, il n'y a aucun moyen de remplir cette contrainte pour la rangée de L'employé, et donc elle n'apparaîtra pas.
d'un autre côté, si l'optimiseur de requête décide de s'attaquer au ministère-employé rejoindre d'abord, puis faire une gauche rejoindre les entreprises, vous les verrez.
donc l'ancienne syntaxe est ambiguë. Il n'y a aucun moyen de spécifier ce que vous voulez, sans traiter avec les indices de requête, et certaines bases de données ont aucun moyen.
entrez la nouvelle syntaxe, avec ceci vous pouvez choisir.
par exemple, si vous voulez toutes les entreprises, comme la description du problème l'indique, voici ce que vous écririez:
SELECT *
FROM Company
LEFT JOIN (
Department INNER JOIN Employee ON Department.ID = Employee.DepartmentID
) ON Company.ID = Department.CompanyID
ici, vous spécifiez que vous voulez que le département-employé join se faire comme une jointure, puis gauche rejoindre les résultats de cela avec les entreprises.
de plus, disons que vous voulez seulement ministères qui contiennent la lettre X en leur nom. Encore une fois, avec les jointures de style ancien, vous risquez de perdre l'entreprise ainsi, si elle n'a pas de départements avec un X dans son nom, mais avec la nouvelle syntaxe, vous pouvez le faire:
SELECT *
FROM Company
LEFT JOIN (
Department INNER JOIN Employee ON Department.ID = Employee.DepartmentID
) ON Company.ID = Department.CompanyID AND Department.Name LIKE '%X%'
cette clause supplémentaire est utilisée pour l'assemblage, mais n'est pas un filtre pour toute la rangée. Ainsi, la rangée peut apparaître avec l'information de l'entreprise, mais peut avoir NULLs dans toutes les colonnes de ministère et d'employé pour cette rangée, parce qu'il n'y a pas département avec un X à son nom pour cette société. C'est dur avec l'ancienne syntaxe.
C'est pourquoi, parmi d'autres vendeurs, Microsoft a déprécié l'ancienne syntaxe externe de jointure, mais pas l'ancienne syntaxe interne de jointure, depuis SQL Server 2005 et plus. La seule façon de parler à une base de données tournant sur Microsoft SQL Server 2005 ou 2008, en utilisant l'ancien style de la syntaxe de jointure externe, est de mettre cette base de données en mode de compatibilité 8.0 (alias SQL Server 2000).
En outre, l'ancienne façon, en jetant un tas de tables à l'optimiseur de requête, avec un tas de clauses où, était semblable à dire "vous êtes ici, faites du mieux que vous pouvez". Avec la nouvelle syntaxe, l'optimiseur de requête a moins de travail à faire afin de déterminer quelles parties vont ensemble.
Donc voilà.
left and INNER JOIN est la vague du futur.
la syntaxe de jointure maintient les conditions près de la table à laquelle elles s'appliquent. Ceci est particulièrement utile lorsque vous rejoignez un grand nombre de tables.
soit dit en passant, vous pouvez faire une jointure externe avec la première syntaxe aussi:
WHERE a.x = b.x(+)
ou
WHERE a.x *= b.x
ou
WHERE a.x = b.x or a.x not in (select x from b)
le premier moyen est l'ancienne norme. La deuxième méthode a été introduite dans SQL-92, http://en.wikipedia.org/wiki/SQL . La norme complète peut être consultée à http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt .
il a fallu de nombreuses années avant que les sociétés de bases de données adoptent la norme SQL-92.
donc la raison pour laquelle la deuxième méthode est préférée, c'est la norme SQL selon le ANSI et Comité de normalisation ISO.
le second est préféré parce qu'il est beaucoup moins susceptible d'entraîner une jonction croisée accidentelle en oubliant de mettre dans la clause où. Une join avec NO on clause échouera la vérification de syntaxe, un vieux style join avec no where clause n'échouera pas, il fera une jointure croisée.
en outre, lorsque vous avez plus tard à gauche rejoindre, il est utile pour la maintenance qu'ils soient tous dans la même structure. Et l'ancienne syntaxe est périmée depuis 1992, c'est bien le passé cesser de l'utiliser.
plus j'ai trouvé que beaucoup de gens qui utilisent exclusivement la première syntaxe ne comprennent pas vraiment joins et comprendre joins est critique pour obtenir des résultats corrects lors de la interrogation.
en gros, quand votre de la clause liste des tableaux comme cela:
SELECT * FROM
tableA, tableB, tableC
le résultat est un produit croisé de toutes les lignes dans les tableaux A, B, C. Ensuite, vous appliquez la restriction WHERE tableA.id = tableB.a_id qui jeter un grand nombre de lignes, puis d'autres ... AND tableB.id = tableC.b_id et vous devriez alors obtenir uniquement les lignes que vous êtes vraiment intéressé.
DBMSs savoir comment optimiser ce SQL afin que la différence de performance à l'écriture de cette utilisation jointures est négligeable (le cas échéant). L'utilisation de la notation de jointure rend l'instruction SQL plus lisible (IMHO, n'utilisant pas les jointures transforme l'instruction en un mess). En utilisant le produit croisé, vous devez fournir des critères de jointure dans la clause où, et c'est le problème avec la notation. Vous empiétez sur votre clause WHERE avec des trucs comme
tableA.id = tableB.a_id
AND tableB.id = tableC.b_id
qui n'est utilisé que pour limiter le produit croisé. Où la clause devrait seulement contenir des RESTRICTIONS à la série de résultats. Si vous mixez table join criteria avec restrictions resultset, vous (et d'autres) trouverez votre requête plus difficile à lire. Vous devriez certainement utiliser jointures et garder la clause a de la clause, et le où clause A où.
je pense qu'il y a de bonnes raisons sur cette page d'adopter la deuxième méthode-en utilisant des jointures explicites. Le point décisif est toutefois que lorsque les critères de jointure sont supprimés de la clause WHERE, il devient beaucoup plus facile de voir les critères de sélection restants dans la clause WHERE.
dans les énoncés sélectifs vraiment complexes, il devient beaucoup plus facile pour un lecteur de comprendre ce qui se passe.
la syntaxe SELECT * FROM table1, table2, ... est acceptable pour quelques tables, mais elle devient exponentiellement ( pas nécessairement un énoncé mathématiquement précis ) de plus en plus difficile à lire au fur et à mesure que le nombre de tables augmente.
la syntaxe de jointure est plus difficile à écrire (au début), mais elle le rend explicite quels critères affectent quelles tables. Cela le rend beaucoup plus difficile de faire une erreur.
aussi, si tous les joints sont intérieurs, alors les deux les versions sont équivalentes. Cependant, au moment où vous avez un joint externe n'importe où dans la déclaration, les choses deviennent beaucoup plus compliquées et il est pratiquement garanti que ce que vous écrivez ne sera pas la question ce que vous pensez que vous avez écrit.
quand vous avez besoin d'une jointure externe la deuxième syntaxe est pas toujours requis:
Oracle:
SELECT a.foo, b.foo
FROM a, b
WHERE a.x = b.x(+)
MSSQLServer (bien qu'il ait été déprécié dans la version 2000) / Sybase:
SELECT a.foo, b.foo
FROM a, b
WHERE a.x *= b.x
mais revenons à votre question. Je ne connais pas la réponse, mais elle est probablement liée au fait qu'un joindre est plus naturel (syntaxiquement, à moins) que l'ajout d'une expression à une où clause lorsque vous faites exactement cela: joindre .
j'entends beaucoup de gens se plaignent le premier est trop difficile à comprendre et qu'il n'est pas clair. Je ne vois pas de problème avec cela, mais après avoir eu cette discussion, j'utilise la seconde même sur les jointures intérieures pour la clarté.
à la base de données, ils finissent par être les mêmes. Pour vous, cependant, vous aurez à utiliser cette seconde syntaxe dans certaines situations. Pour le plaisir d'éditer des requêtes qui finissent par devoir l'utiliser (en découvrant que vous aviez besoin d'une jointure gauche où vous aviez une jointure droite), et pour la cohérence, Je ne dessinerais que sur la 2ème méthode. Il va faire de la lecture de requêtes facile.
Eh bien la première et la deuxième requêtes peuvent donner des résultats différents parce qu'une jointure gauche inclut tous les enregistrements de la première table, même s'il n'y a pas d'enregistrements correspondants dans la table de droite.