StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL query and join: why?
text
Body
copied!<p>I'm sorry if this is a newbie question, but it seems I don't get why this doesn't work like I would like:</p> <pre><code>mysql> select t.id,t.date_fin_val,tc.date_fin_val from tiers t join tiers_critere_int tc on tc.id_tiers=t.id where (t.date_fin_val is null) and (tc.date_fin_val is null); +----+---------------------+---------------------+ | id | date_fin_val | date_fin_val | +----+---------------------+---------------------+ | 1 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | | 1 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | | 1 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | +----+---------------------+---------------------+ 3 rows in set (0.00 sec) mysql> select t.id,t.date_fin_val,tc.date_fin_val from tiers t left outer join tiers_critere_int tc on tc.id_tiers=t.id where (t.date_fin_val is null) and (tc.date_fin_val is null); Empty set (0.00 sec) mysql> </code></pre> <p>I thought that "left outer joins" means: "if there is no result on the right side, but one on the left, go on <strong>anyway</strong> with the one on the left and put "<code>null</code>" values on the right. If I were right, the second query with "<code>left outer join</code>" instead of "<code>join</code>" should return values. But it doesn't. Why?</p> <p>Here are my datas:</p> <pre><code>mysql> select * from tiers t where date_fin_val is null; +----+---------------------+--------------------+ | id | date_fin_val | est_tiers_physique | +----+---------------------+--------------------+ | 1 | 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_fin_val | id_tiers | id_critere | critere | +----+---------------------+----------+------------+---------+ | 1 | 0000-00-00 00:00:00 | 1 | 2 | 86 | | 2 | 0000-00-00 00:00:00 | 1 | 6 | 170 | | 3 | 0000-00-00 00:00:00 | 1 | 7 | 65 | +----+---------------------+----------+------------+---------+ 3 rows in set (0.00 sec) mysql> </code></pre> <p>Thank you very much!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload