SQL join and left outer join: why are the results different? [duplicate]

Question

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?

Answer 1

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).