MySQL Forums
Forum List  »  Newbie

JOIN, and LEFT OUTER JOIN: I don't get it
Posted by: Olivier Dofus
Date: March 13, 2011 05:17AM

Look closely at the two last queries: they're the same except that the first is a "join" and the second is a "left outer join".

Maybe I didn't understing "left outer join" but from my pov. it *should* return the same rows (with the actual data below) than the first query!

I'm stuck!


mysql> select * from tiers where date_fin_val is null;
+----+---------------------+---------------------+--------------------+
| id | date_debut_val | date_fin_val | est_tiers_physique |
+----+---------------------+---------------------+--------------------+
| 1 | 2011-03-13 06:07:05 | 0000-00-00 00:00:00 | 1 |
+----+---------------------+---------------------+--------------------+
1 row in set (0.00 sec)

mysql> select * from tiers_critere_int where date_fin_val is null;
+----+---------------------+---------------------+----------+------------+---------+
| id | date_debut_val | date_fin_val | id_tiers | id_critere | critere |
+----+---------------------+---------------------+----------+------------+---------+
| 1 | 2011-03-13 06:07:05 | 0000-00-00 00:00:00 | 1 | 2 | 86 |
| 2 | 2011-03-13 06:07:05 | 0000-00-00 00:00:00 | 1 | 6 | 170 |
| 3 | 2011-03-13 06:07:05 | 0000-00-00 00:00:00 | 1 | 7 | 65 |
+----+---------------------+---------------------+----------+------------+---------+
3 rows in set (0.00 sec)

mysql> select * from tiers t
join tiers_critere_int tci
on tci.id_tiers=t.id
where (t.date_fin_val is null) and (tci.date_fin_val is null);
+----+---------------------+---------------------+--------------------+----+---------------------+---------------------+----------+------------+---------+
| id | date_debut_val | date_fin_val | est_tiers_physique | id | date_debut_val | date_fin_val | id_tiers | id_critere | critere |
+----+---------------------+---------------------+--------------------+----+---------------------+---------------------+----------+------------+---------+
| 1 | 2011-03-13 06:07:05 | 0000-00-00 00:00:00 | 1 | 1 | 2011-03-13 06:07:05 | 0000-00-00 00:00:00 | 1 | 2 | 86 |
| 1 | 2011-03-13 06:07:05 | 0000-00-00 00:00:00 | 1 | 2 | 2011-03-13 06:07:05 | 0000-00-00 00:00:00 | 1 | 6 | 170 |
| 1 | 2011-03-13 06:07:05 | 0000-00-00 00:00:00 | 1 | 3 | 2011-03-13 06:07:05 | 0000-00-00 00:00:00 | 1 | 7 | 65 |
+----+---------------------+---------------------+--------------------+----+---------------------+---------------------+----------+------------+---------+
3 rows in set (0.00 sec)

mysql> select * from tiers t
left outer join tiers_critere_int tci
on tci.id_tiers=t.id
where (t.date_fin_val is null) and (tci.date_fin_val is null);
Empty set (0.00 sec)

mysql>

Options: ReplyQuote


Subject
Written By
Posted
JOIN, and LEFT OUTER JOIN: I don't get it
March 13, 2011 05:17AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.