参见英文答案 >
What is the difference between “INNER JOIN” and “OUTER JOIN”? 31个
我有一张桌子. partenaire可能有一个或多个地址.当然,一个地址可能“属于”不止一个partenaire.所以我有3张桌子：partenaire,partenaire_adresse和adresse.地址只有一个城镇(法语为ville)所以我在表格中有一个外键id_ville.
SELECT
p.nom,
v.nom, v.id_region as id_r, v.id_departement as id_p,
r.description as region
FROM partenaire p
JOIN partenaire_adresse pa
ON pa.id_partenaire=p.id
JOIN adresse a
ON a.id=pa.id_adresse
JOIN ville v
ON v.id=a.id_ville
JOIN region r
ON v.id_region=r.id
LIMIT 4;
这给了我那些结果：
+----------------------------+-------------+------+------+--------+
| nom | nom | id_r | id_p | region |
+----------------------------+-------------+------+------+--------+
| Ferme Auberge Christlesgut | Breitenbach | 1 | 2 | Alsace |
| Alice Pizza | Strasbourg | 1 | 1 | Alsace |
| Au Vieux Cellier | Strasbourg | 1 | 1 | Alsace |
| Auberge du 7Eme Art | Strasbourg | 1 | 1 | Alsace |
+----------------------------+-------------+------+------+--------+
现在如果我在最后一个表(区域)上执行LEFT OUTER JOIN,结果就不一样了：
SELECT
p.nom,
v.nom, v.id_region as id_r, v.id_departement as id_p,
r.description as region
FROM partenaire p
JOIN partenaire_adresse pa
ON pa.id_partenaire=p.id
JOIN adresse a
ON a.id=pa.id_adresse
JOIN ville v
ON v.id=a.id_ville
LEFT OUTER JOIN region r
ON v.id_region=r.id
LIMIT 4;
结果不一样.看到：
+---------------------+----------+------+------+----------------+
| nom | nom | id_r | id_p | region |
+---------------------+----------+------+------+----------------+
| 'Le 144' Petrossian | Paris 18 | 12 | 43 | Île-de-France |
| 'Le 144' Petrossian | Paris 08 | 12 | 43 | Île-de-France |
| 'O'Quai' | Vouvray | 7 | 26 | Centre |
| 'O'Quai' | Tours | 7 | 26 | Centre |
+---------------------+----------+------+------+----------------+
我认为它不应该,因为在第一个查询中,id_region和id_departement不为null,所以如果你执行“JOIN”或“LEFT OUTER JOIN”,结果应该是相同的.或者我错过了什么？
5451
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
