SQL query and join: why?

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:

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> 

I thought that "left outer joins" means: "if there is no result on the right side, but one on the left, go on anyway with the one on the left and put "null" values on the right. If I were right, the second query with "left outer join" instead of "join" should return values. But it doesn't. Why?

Here are my datas:

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>

Thank you very much!

My results are correct with you data and queries.

CREATE TABLE `tiers` (
  `id` int(11) DEFAULT NULL,
  `date_fin_val` datetime DEFAULT NULL,
  `est_tiers_physique` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

insert into tiers (id, est_tiers_physique) values (1, 1);

select * from tiers;
+------+--------------+--------------------+
| id   | date_fin_val | est_tiers_physique |
+------+--------------+--------------------+
|    1 | NULL         |                  1 |
+------+--------------+--------------------+
1 row in set (0.00 sec)

CREATE TABLE `tiers_critere_int` (
  `id` int(11) DEFAULT NULL,
  `date_fin_val` datetime DEFAULT NULL,
  `id_tiers` int(11) DEFAULT NULL,
  `id_critere` int(11) DEFAULT NULL,
  `critere` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

insert into tiers_critere_int (id, id_tiers, id_critere, critere) 
values 
(1, 1, 2, 86),
(2, 1, 6, 170),
(3, 1, 7, 65)
;

select * from tiers_critere_int;
+------+--------------+----------+------------+---------+
| id   | date_fin_val | id_tiers | id_critere | critere |
+------+--------------+----------+------------+---------+
|    1 | NULL         |        1 |          2 |      86 |
|    2 | NULL         |        1 |          6 |     170 |
|    3 | NULL         |        1 |          7 |      65 |
+------+--------------+----------+------------+---------+
3 rows in set (0.00 sec)

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 | NULL         | NULL         |
|    1 | NULL         | NULL         |
|    1 | NULL         | NULL         |
+------+--------------+--------------+
3 rows in set (0.00 sec)

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);
+------+--------------+--------------+
| id   | date_fin_val | date_fin_val |
+------+--------------+--------------+
|    1 | NULL         | NULL         |
|    1 | NULL         | NULL         |
|    1 | NULL         | NULL         |
+------+--------------+--------------+
3 rows in set (0.02 sec)

Now if you insert an empty string you will have:

insert into tiers (id, date_fin_val, est_tiers_physique) values (2, '', 1);
Query OK, 1 row affected, 1 warning (0.00 sec)

select * from tiers;
+------+---------------------+--------------------+
| id   | date_fin_val        | est_tiers_physique |
+------+---------------------+--------------------+
|    1 | NULL                |                  1 |
|    2 | 0000-00-00 00:00:00 |                  1 |
+------+---------------------+--------------------+
2 rows in set (0.00 sec)

It seems there's a bug in MySQL. Here's how I created my tables:

CREATE TABLE tiers_critere_int (
  id bigint AUTO_INCREMENT NOT NULL,
  date_debut_val datetime not null,
  date_fin_val datetime **default NULL**,
  id_tiers bigint(20) NOT NULL,
  id_critere bigint(20) NOT NULL,
  critere bigint(20) NOT NULL,
  PRIMARY KEY  (id,date_debut_val,date_fin_val),
  KEY id (id),
  KEY date_debut_val (date_debut_val),
  KEY date_fin_val (date_fin_val),
  KEY date_debut_val_2 (date_debut_val,date_fin_val),
  KEY critere (critere),
  KEY id_tiers (id_tiers),
  KEY id_critere (id_critere),
  FOREIGN KEY (id_tiers) REFERENCES tiers (id)
  ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (id_critere) REFERENCES critere (id)
  ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

In tiers_critere_int, date_fin_val is null by default. That's what you think. But if you do:

mysql> update tiers_critere_int set date_fin_val= null;
Query OK, 0 rows affected, 3 warnings (0.00 sec)
Rows matched: 3  Changed: 0  Warnings: 3

mysql> show warnings;
+---------+------+--------------------------------------+
| Level   | Code | Message                              |
+---------+------+--------------------------------------+
| Warning | 1048 | Column 'date_fin_val' cannot be null | 
| Warning | 1048 | Column 'date_fin_val' cannot be null | 
| Warning | 1048 | Column 'date_fin_val' cannot be null | 
+---------+------+--------------------------------------+
3 rows in set (0.00 sec)

mysql> 

The problem is that MySQL does accept the creation of the table, but does not accept null values for keys. But what is not normal, is that this query works and that's clearly surprising (or not normal it's up to you to judge):

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> 

So that whas really confusing, and what was more confusing is that the clause "is null" is properly handled in the clause where (t.date_fin_val is null), but it's not properly handled in the clause and (tci.date_fin_val is null) whereas it's in the same request, on tables created the same way, on the same columns.

Thus, the end of the story: the right request is:

mysql> select t.id,t.date_fin_val,tci.date_fin_val
from tiers t
left outer join tiers_critere_int tci
on t.id=tci.id_tiers
where (t.date_fin_val ='0000-00-00 00:00:00')
and (tci.date_fin_val ='0000-00-00 00:00:00');
+----+---------------------+---------------------+
| 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> 

I think that this has to do with how MySQL optimizes LEFT (and RIGHT) JOINs in combination with the date columns being NULLs.

The LEFT OUTER JOIN on the same condition and the same data should return more or the same number of rows as INNER JOIN.

Please check:

1. Your data to see if they are the same for the two queries
2. Whether the two queries are as you specified in the question (e.g. have you made a typo)

Woa --- I think you are all missing something:

This is the current left join:

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

The left join condition should probably be this:

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 AND (tc.date_fin_val is null)
where (t.date_fin_val is null);

The fact that you were still filtering rows in the WHERE clause is hiding what you are trying to do with your LEFT OUTER JOIN.