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

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?

Add an order by to each query to make any sort of meaningful comparison based on a few records at the front. — Pieter Geerkens, Apr 02 '13 at 22:13

score 4 · Accepted Answer · answered Apr 02 '13 at 22:16

4

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

answered Apr 02 '13 at 22:16

didierc

14,572
3
32
52

I've added "order by p.nom" and it works for both. What I dont get is that there's a key on "nom" of the table partenaire, and with the "join" it takes 28.9s, and with the "left outer join", it takes 0.10s! How comes? – Olivier Pons Apr 02 '13 at 22:32
Quite honestly I don't know. What were the timings like without the order by? you could try using `EXPLAIN` on both queries to see how differently they are planned. – didierc Apr 02 '13 at 22:38
+1 for `explain`. I'll try this. As for the "without the order by", it took 0.10s. – Olivier Pons Apr 03 '13 at 06:37
Interesting, feel free to post your finding as an update to your question, or ask a new question if there are no existing equivalent question. – didierc Apr 03 '13 at 08:15

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

1 Answers1