MySQL Forums :: Newbie :: JOIN, and LEFT OUTER JOIN: I don't get it


Advanced Search

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 Olivier Dofus 03/13/2011 05:17AM
Re: JOIN, and LEFT OUTER JOIN: I don't get it Guelphdad Lake 03/13/2011 09:29AM
Re: JOIN, and LEFT OUTER JOIN: I don't get it Rick James 03/13/2011 01:57PM
Re: JOIN, and LEFT OUTER JOIN: I don't get it Gints Plivna 03/14/2011 04:33AM
Re: JOIN, and LEFT OUTER JOIN: I don't get it Rick James 03/14/2011 08:10AM
Re: JOIN, and LEFT OUTER JOIN: I don't get it Gints Plivna 03/14/2011 08:34AM


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.