Inner join avec condition

Bonjour,

Voici une question théorique sql, quelque soit le sgbdr il me semble.
Je voulais savoir quelle est la différence entre ces 2 requêtes :

Code:

---

1 2 3 4

Select a.*, b.* from t1 a inner join t2 b on (a.c1 = b.c1) where a.c2 = 'xx';

---

et

Code:

---

1 2 3

Select a.*, b.* from t1 a inner join t2 b on (a.c1 = b.c1 and a.c2 = 'xx');

---

Il me semble que la 2nde requête est plus performante parce que la restriction sur a.c2 se fait dès la jointure.

Mais il me semble aussi que les résultats de ces requêtes ne sont pas toujours les mêmes ?

Qu'en pensez-vous ?

Merci d'avance de vos éclaircissements.

Cordialement.

Dans ce cas de figure, les deux requêtes sont strictement identiques.

Pour la jointure interne les résultats sont identiques, ce qui n'est pas le cas pour la jointure externe. En effet les critères (ON) d'une jointure (JOIN) sont évaluées avant l'opération de jointure et la restriction (WHERE) après.

A +

Merci, vos réponses sont claires, alors pour une jointure externe (on suppose que a.c1 clé étrangère optionnelle (et que peuvent y subsister d'anciennes clés de b n'existant plus (si c'est possible)) et b.c1 clé primaire):

Req1

Code:

---

1 2 3 4

SELECT a.*, b.* FROM t1 a left outer JOIN t2 b ON (a.c1 = b.c1) WHERE b.c1 is null;

---

Req2

Code:

---

1 2 3

SELECT a.*, b.* FROM t1 a left outer JOIN t2 b ON (a.c1 = b.c1 and b.c1 is null);

---

Req3

Code:

---

1 2 3

SELECT a.*, b.* FROM t1 a left outer JOIN t2 b ON (a.c1 = b.c1 and a.c1 is null);

---

Req4

Code:

---

1 2 3 4

SELECT a.*, b.* FROM t1 a left outer JOIN t2 b ON (a.c1 = b.c1) WHERE a.c1 is null;

---

On peut supposer pour :

Req1 : retourne des lignes si a.c1 est une ancienne clé primaire de b n'existant plus (clés primaires nulles de b non autorisées).

Req2 : idem que Req1 ?

Req3 : ne retourne pas lignes ?

Req4 : retourne les lignes de t1 ayant a.c1 null, la jointure est inutile ?

Que pensez-vous de mes suppositions ? Y a surement des erreurs !

Cordialement.

Voir mon blog au sujet de la jointure externe et de la condition de restriction.

Citation:

---

Code:

---

1 2 3 4

SELECT a.*, b.* FROM t1 a LEFT OUTER JOIN t2 b ON (a.c1 = b.c1) WHERE b.c1 IS NULL;

---

Req1 : retourne des lignes si a.c1 est une ancienne clé primaire de b n'existant plus (clés primaires nulles de b non autorisées).

---

Ta requête 1 va retourner toutes les lignes de t1 (a) qui n'ont pas de correspondance dans t2 (b).

Cela n'a pas forcément à voir avec d'anciennes clés primaires.

Soit le MCD suivant :
personne -0,n----diriger----1,1- projet

Tables :
personne (prs_id, prs_nom...)
projet (prj_id, prj_id_chef, prj_libelle...)

Quelles personnes ne dirigent pas de projet ?
=> Celles dont l'identifiant ne figurent pas parmi les valeurs de la clé étrangère dans projet

Code:

---

1 2 3 4

SELECT prs.prs_nom FROM personne prs LEFT OUTER JOIN projet prj ON prj.prj_id_chef = prs.prs_id WHERE prj.prj_id IS NULL

---

Citation:

---

Code:

---

1 2 3 4

SELECT a.*, b.* FROM t1 a LEFT OUTER JOIN t2 b ON (a.c1 = b.c1) WHERE b.c1 IS NULL;

---

Req2 : idem que Req1 ?

---

Hum... pas sûr !
Je crains que le SGBD cherche d'abord les lignes de t2 (b) pour lesquelles la colonne c1 soit à NULL puis tente le rapprochement avec les lignes de t1 (a) ayant aussi c1 à NULL. Et comme NULL n'est en principe égal à rien, même pas à NULL, je crains que la requête ne retourne aucun résultat, même s'il y a effectivement des NULL dans c1 de t1 et de t2.
À vérifier !

Citation:

---

Code:

---

1 2 3

SELECT a.*, b.* FROM t1 a LEFT OUTER JOIN t2 b ON (a.c1 = b.c1 AND a.c1 IS NULL);

---

Req3 : ne retourne pas lignes ?

---

Même comportement que la requête 2 je pense. Là aussi, c'est à vérifier.

Citation:

---

Code:

---

1 2 3 4

SELECT a.*, b.* FROM t1 a LEFT OUTER JOIN t2 b ON (a.c1 = b.c1) WHERE a.c1 IS NULL;

---

Req4 : retourne les lignes de t1 ayant a.c1 null, la jointure est inutile ?

---

Oui je pense.

Mais dans tout ça, comme je démontre avec le cas concret que j'ai donné plus haut, ton postulat de départ est faux :

Citation:

---

(on suppose que a.c1 clé étrangère optionnelle (et que peuvent y subsister d'anciennes clés de b n'existant plus (si c'est possible)) et b.c1 clé primaire):

---

C'est plutôt le contraire.

D'ailleurs, une clé étrangère optionnelle, c'est pas terrible du point de vue modélisation ! Il vaut mieux une colonne indiquant que la ligne est invalide ou supprimer la ligne.

Eh bien il ne me reste qu'à vous remercier, tout ça me semble clair !
Y en a qui veulent rajouter quelque chose ?

Cordialement.

Salut !

J'ai aussi écrit un petit article là-dessus. Il est moche, mais il montre quelques exemples rigolos sur les NULL qu'il ne me semble pas avoir vu chez Cinephile (dont l'article est par contre vachement plus propre, clair et lisible) :
http://pacmann.over-blog.com/article...-38449736.html

Sinon pour aller un peu plus loin sur les remarques de Cinephile, il n'y a pas de question sur l'ordre d'exécution quand tu spécifies :
(a.c1 = b.c1 AND b.c1 IS NULL)
ou
(a.c1 = b.c1 AND a.c1 IS NULL)

Ce sont simplement deux prédicats qui ne seront jamais réalisé, carrément ni true ni false.
Du coup, la ligne de gauche ressort quand même dans le resultset.

Et du coup ce qui est amusant, c'est que même ta requête 3 retourne toutes les lignes de gauche.

Au final, tu pourrais très bien écrire : LEFT OUTER JOIN ON 1 = 2

Citation:

---

D'ailleurs, une clé étrangère optionnelle, c'est pas terrible du point de vue modélisation ! Il vaut mieux une colonne indiquant que la ligne est invalide ou supprimer la ligne.

---

Une table de jointure c'est sans doute aussi propre (?)
On créé une ligne dans cette table quand il y a une jointure entre une ligne de a et une ligne de b.