SQL join and left outer join: why are the results different?
mysql, sql
Solution
The issue you are experiencing is related to the fact that SQL makes no guarantee on the order of the result when there is no order specified. If you add an ORDER BY clause, you should get the same results, provided that the tables have matching keys on all rows (of course).
Problem
I have a table `partenaire`. A partenaire may have one or more addresses. And of course one address may "belong" to more than one partenaire. So I have 3 tables: `partenaire`, `partenaire_adresse` and `adresse`. The address has only one town (`ville` in French) so I have a foreign key `id_ville` in the table `addresse`. ``` 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; ``` This gives me those results: ``` +----------------------------+-------------+------+------+--------+ | 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 | +----------------------------+-------------+------+------+--------+ ``` Now if I do a `LEFT OUTER JOIN` on the last table (`region`) the results are not the same: ``` 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; ``` Not the same results. See: ``` +---------------------+----------+------+------+----------------+ | 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 | +---------------------+----------+------+------+----------------+ ``` I think it should not, because in the first query, id_region and id_departement are not null, so if you do a "JOIN" or a "LEFT OUTER JOIN", the results should be the same. Or am I missing something?