Discussion :

[polo7]

Hello tous,
J'ai un trou... passager j'espère.
J'aimerais obtenir la liste des items de la table1 en excluant les items qui sont dans la table2 (table associative) en fournissant un id_table3.
Si id_table3=2 (truc2) Il faut sortir:
info2 et info4

info1 et info3 sont exclus car truc2 en fait déjà partie.

table1(id_table1,name)
1 info1
2 info2
3 info3
4 info4

table2(id_table1,id_table3)
1 2
1 3
2 4
3 2

table3(id_table3,truc)
1 truc1
2 truc2
3 truc3
4 truc4


Est-ce clair ?

Merci de vos suggestion.

[ericd69]

salut,

un truc comme ça:

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
select t1.name,t3.truc
from table2 t2
inner join table1 t1 on t2.id_table1=t1.id_table1
inner join table3 t3 on t2.id_table3=t3.id_table3
where id_table3<>2

[polo7]

Hello,
Merci bien pour l'aide, mais cette jointure ne fonctionne pas comme je veux. Cependant, entre-temps j'ai fait ceci qui fonctionne. En fait, il n'est pas nécessaire de faire un requête sur la table3, je le fait directement avec id3 sur la table2.
Y-a-t-il plus performant et plus simple ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
SELECT * 
FROM table1 
WHERE table1.id1 
NOT IN 
(SELECT table2.id1 
FROM table2 
WHERE table2.id2=2)

[StringBuilder]

NOT IN + SOUS SELECT = LEFT OUTER JOIN + WHERE COLONE IS NULL

Exemple :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
select t1.id
from t1
where t1.id not in (select t2.val from t2);

Revient à dire :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
select t1.id
from t1
left outer join t2 on t2.val = t1.id
where t2.val is null;

Au détail près que la seconde syntaxe est plus propre et peut être plus rapide sur des requêtes complexes qui perdent l'optimiseur.

[polo7]

Merci bien pour l'équivalence avec LEFT OUTER JOIN.
la requête fonctionne, mais elle sort uniquement les enregistrements de table1 qui ne sont pas dans table2, mais moi je souhaite obtenir celà, mais uniquement pour une valeur id3=2 de la table2.
Exemple: une personne (table3) inscrite à un cours (table1) et je propose à cette personne de s'inscrire uniquement aux cours auxquels elle n'est pas inscrite. table2 étant la table associative.

[StringBuilder]

Il suffit de rajouter un INNER JOIN sur T3 pour n'exclure que les lignes de T2 qui correspondent à des lignes de T3.

[ericd69]

déjà * c'est le mal surtout avec des jointures...

ça fait ce que tu demandais sans plus d'info...

après c'est à toi de l'adapter en fonction de tes besoins... pas besoin de table3, donc:

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT t1.name
FROM table2 t2
INNER JOIN table1 t1 ON t2.id_table1=t1.id_table1
WHERE t2.id_table3<>2

attention ce que tu dis est plutôt un cas particulier StringBuilder, en fait, si on veut être totalement générique:
NOT IN + SOUS SELECT = inner join...on avec dans where <> au lieu de = pour la condition
ce qui peut aussi s'écrire:

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT t1.name
FROM table2 t2
INNER JOIN table1 t1 ON t2.id_table1=t1.id_table1 and t2.id_table3<>2

mais je trouve qu'il est mieux de ne pas utiliser cette forme car tu sépares mieux sur quelle table s'applique la condition de restriction avec le where

ce qui rend la version jointure plus rapide c'est que, si ça touche des colonnes indexées, y a aucun accès à la table jointe et pas de table temporaire interne intermédiaire alors que la sous-requête en engendre une sur laquelle est appliqué l'opérateur not in...

not in est surtout utile si tu veux tester un jeu de valeurs:

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

... not in (1,10,5,15)

car il l'optimise comme de tests d'égalité séparés par des or

[Rei Ichido]

déjà * c'est le mal surtout avec des jointures...

ça fait ce que tu demandais sans plus d'info...

après c'est à toi de l'adapter en fonction de tes besoins... pas besoin de table3, donc:

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT t1.name
FROM table2 t2
INNER JOIN table1 t1 ON t2.id_table1=t1.id_table1
WHERE t2.id_table3<>2

Sauf que s'il y a plusieurs enregistrements correspondants dans table2, on multiplie le nombre de résultats, ce qui n'est pas du tout le comportement initial.

J'en profite pour rappeler que le NOT IN (SELECT ... ) a un comportement qui peut sembler étrange dès lors qu'il y a un NULL dans le SELECT (forcément, si le select retourne NULL, on ne peut plus garantir qu'une quelconque valeur n'est pas égale, donc plus aucune ligne ne sera sélectionnée). Quand on parle de clés primaires, ça n'arrive pas, mais ce genre de joyeusetés se retrouve tellement souvent ...

Pour être propre dans tous les cas, utiliser plutôt NOT EXISTS.

[StringBuilder]

Non, NOT IN se traduit par un LEFT OUTER JOIN avec WHERE NULL

Le INNER JOIN ne fonctionne pas du tout !

Soit une table CLIENT avec la liste des clients.
Une table LIVRAISON avec la liste des livraisons.

Le résultat (je te laisse admirer ce que fait le INNER JOIN <>)

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
 
select c.cli_id
from client c;
 
cli_id
-----------
1
2
3
4
 
(4 row(s) affected)
 
select l.cli_id
from livraison l;
 
cli_id
-----------
2
3
3
 
(3 row(s) affected)
 
select c.cli_id
from client c
where c.cli_id not in (select l.cli_id from livraison l);
 
cli_id
-----------
1
4
 
(2 row(s) affected)
 
select c.cli_id
from client c
inner join livraison l on l.cli_id <> c.cli_id;
 
cli_id
-----------
1
3
4
1
2
4
1
2
4
 
(9 row(s) affected)
 
select c.cli_id
from client c
left outer join livraison l on l.cli_id = c.cli_id
where l.cli_id is null;
 
cli_id
-----------
1
4
 
(2 row(s) affected)

[StringBuilder]

Sauf que s'il y a plusieurs enregistrements correspondants dans table2, on multiplie le nombre de résultats, ce qui n'est pas du tout le comportement initial.

J'en profite pour rappeler que le NOT IN (SELECT ... ) a un comportement qui peut sembler étrange dès lors qu'il y a un NULL dans le SELECT (forcément, si le select retourne NULL, on ne peut plus garantir qu'une quelconque valeur n'est pas égale, donc plus aucune ligne ne sera sélectionnée). Quand on parle de clés primaires, ça n'arrive pas, mais ce genre de joyeusetés se retrouve tellement souvent ...

Pour être propre dans tous les cas, utiliser plutôt NOT EXISTS.

Non seulement ça duplique tout, mais surtout ça ramène aussi les lignes qu'on ne veut pas.

Et je persiste et signe, LEFT OUTER JOIN est LA solution PROPRE.
Il n'y a pas d'exception possible.

[Rei Ichido]

Pourquoi LA solution propre, alors que NOT EXISTS a le même effet, mais est (à mon avis) plus lisible ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
SELECT c.cli_id
FROM client c
WHERE NOT EXISTS (SELECT 1 FROM livraison l WHERE l.cli_id = c.cli_id)

[punkoff]

Non, NOT IN se traduit par un LEFT OUTER JOIN avec WHERE NULL

Vous seriez surpris de ce que l'on peut avoir comme résultat avec un NOT IN.

Je rejoins Rei Ichido sur ses commentaires.

Je n'utilise pas de not in quand on réalise un test de non existence

Quand à la meilleur syntaxe, tout bon SGBD va traduire l'approche NOT EXISTS / LEFT OUTER JOIN de la même manière...


Pour MySql en particulier je ne sais pas par contre.

[StringBuilder]

Donc pour reprendre le problème initial, avec 3 table dont une de correspondance, je veux proposer au client "Client 3" les cours pour lesquels il n'est pas inscrit :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
 
select cli_id
from client;
 
cli_id
-----------
1
2
3
4
 
(4 row(s) affected)
 
select cou_id
from cours;
 
cou_id
-----------
1
2
3
4
 
(4 row(s) affected)
 
select cli_id, cou_id
from inscription;
 
cli_id      cou_id
----------- -----------
1           1
2           2
2           3
2           4
3           1
3           2
 
(6 row(s) affected)
 
select co.cou_id
from cours co
where co.cou_id not in (select i.cou_id from inscription i inner join client cl on cl.cli_id = i.cli_id where cl.cli_nom = 'Client 3');
 
cou_id
-----------
3
4
 
(2 row(s) affected)
 
select co.cou_id
from cours co
left outer join (
  inscription i 
  inner join client cl on cl.cli_id = i.cli_id and cl.cli_nom = 'Client 3'
) on i.cou_id = co.cou_id
where cl.cli_id is null;
 
cou_id
-----------
3
4
 
(2 row(s) affected)

Attention aux parenthèses au niveau de la jointure cependant !

[StringBuilder]

Pourquoi LA solution propre, alors que NOT EXISTS a le même effet, mais est (à mon avis) plus lisible ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
SELECT c.cli_id
FROM client c
WHERE NOT EXISTS (SELECT 1 FROM livraison l WHERE l.cli_id = c.cli_id)

Parce que le SGBD va au mieux (si l'optimiseur n'est pas perdu) transformer la sous-requête en LEFT OUTER JOIN.

Les sous-requête forcent le SGBD à se comporter de manière itérative alors que le LEFT OUTER JOIN lui permet de travailler de façon ensembliste.
Quand l'optimiseur est à la rue, il va commencer à travailler de façon itérative (et évaluer le sous-select pour chaque tuple du reste de la requête) et adieu les perfs.

[StringBuilder]

Vous seriez surpris de ce que l'on peut avoir comme résultat avec un NOT IN.

Je rejoins Rei Ichido sur ses commentaires.

Je n'utilise pas de not in quand on réalise un test de non existence

Quand à la meilleur syntaxe, tout bon SGBD va traduire l'approche NOT EXISTS / LEFT OUTER JOIN de la même manière...


Pour MySql en particulier je ne sais pas par contre.

Je suis d'accord pour le NOT IN, cependant, le LEFT OUTER JOIN est d'ailleurs plutôt synonyme du NOT EXISTS, donc ça tombe bien

[ericd69]

relis bien ma requête ce n'est pas du tout celle que tu proposes...

toi:

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT c.cli_id
FROM client c
INNER JOIN livraison l ON l.cli_id <> c.cli_id;

moi:

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT t1.name
FROM table2 t2
INNER JOIN table1 t1 ON t2.id_table1=t1.id_table1
WHERE t2.id_table3<>2

ce n'est pas du tout la même forme de jointure

ne fais jamais confiance au mail que t'envoie le forum mais toujours à ce qui est marquer sur le forum car tu ne vois pas les modifications autrement...



je viens de tester le inner que j'ai fait, en effet il ne ramène pas ce qu'il faut...la chaleur me ramollit les neurones

par contre Rei Ichido a raison ce n'est pas LA seule requête de remplacement la sienne fera la même chose...

[polo7]

Les deux solutions proposées par StringBuilder fonctionnent.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
SELECT co.cou_id
FROM cours co
WHERE co.cou_id NOT IN (SELECT i.cou_id FROM inscription i INNER JOIN client cl ON cl.cli_id = i.cli_id WHERE cl.cli_nom = 'Client 3');

mais c'est plus long que ma requete qui ne tape pas dans la table client:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
SELECT co.cou_id
FROM cours co
WHERE co.cou_id NOT IN (SELECT i.cou_id FROM inscription i WHERE i.cli_id = '3');

La deuxième proposée donne le même résultat mais sans sous-requête, cependant, elle tape dans la table client ce qui n'est pas nécessaire:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
SELECT co.cou_id
FROM cours co
LEFT OUTER JOIN (
  inscription i 
  INNER JOIN client cl ON cl.cli_id = i.cli_id AND cl.cli_nom = 'Client 3'
) ON i.cou_id = co.cou_id
WHERE cl.cli_id IS NULL;

J'ai essayé en remplaçant
INNER JOIN client cl ON cl.cli_id = i.cli_id AND cl.cli_nom = 'Client 3'
par
WHERE i.cli_id = '3'
sans succès.

[StringBuilder]

Ben c'est toi qui parle de 3 tables...

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
SELECT co.cou_id
FROM cours co
LEFT OUTER JOIN inscription i ON i.cou_id = co.cou_id and i.cli_id = 3
WHERE cl.cli_id IS NULL;

Si tu n'as connais déjà l'ID de ta troisième table, alors la requête ci-dessus marchera aussi.