Re: JOIN, and LEFT OUTER JOIN: I don't get it

[Gints Plivna]

Hmmmm, actually there is something very strange about that.
These additional predicates SHOULD NOT make any changes in result set. Here are mine results:

Server version: 5.4.3-beta-community MySQL Community Server (GPL)
mysql> select * from t where fin_date is null;
+------+----------+
| id   | fin_date |
+------+----------+
|    1 | NULL     |
+------+----------+
1 row in set (0.00 sec)

mysql> select * from c where fin_date is null;
+------+------+----------+
| id   | t_id | fin_date |
+------+------+----------+
|    1 |    1 | NULL     |
|    2 |    1 | NULL     |
|    3 |    1 | NULL     |
+------+------+----------+
3 rows in set (0.00 sec)
mysql> select * from t
    -> join c on c.t_id = t.id
    -> where t.fin_date is null and c.fin_date is null;
+------+----------+------+------+----------+
| id   | fin_date | id   | t_id | fin_date |
+------+----------+------+------+----------+
|    1 | NULL     |    1 |    1 | NULL     |
|    1 | NULL     |    2 |    1 | NULL     |
|    1 | NULL     |    3 |    1 | NULL     |
+------+----------+------+------+----------+
3 rows in set (0.00 sec)

mysql> select * from t
    -> left outer join c on c.t_id = t.id
    -> where t.fin_date is null and c.fin_date is null;
+------+----------+------+------+----------+
| id   | fin_date | id   | t_id | fin_date |
+------+----------+------+------+----------+
|    1 | NULL     |    1 |    1 | NULL     |
|    1 | NULL     |    2 |    1 | NULL     |
|    1 | NULL     |    3 |    1 | NULL     |
+------+----------+------+------+----------+
3 rows in set (0.00 sec)

Could you issue explain extended and then show warnings to see what is actual query?
like this:

mysql> explain extended
    -> select * from t
    -> left outer join c on c.t_id = t.id
    -> where t.fin_date is null and c.fin_date is null;
+----+-------------+-------+------+---------------+------+---------+------+-----
-+----------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows
 | filtered | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+----------+-------------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL |    1
 |   100.00 | Using where |
|  1 | SIMPLE      | c     | ALL  | NULL          | NULL | NULL    | NULL |    3
 |   100.00 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+----------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---+
| Level | Code | Message



   |
+-------+------+----------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---+
| Note  | 1003 | select `test`.`t`.`id` AS `id`,`test`.`t`.`fin_date` AS `fin_da
te`,`test`.`c`.`id` AS `id`,`test`.`c`.`t_id` AS `t_id`,`test`.`c`.`fin_date` AS
 `fin_date` from `test`.`t` left join `test`.`c` on((`test`.`c`.`t_id` = `test`.
`t`.`id`)) where (isnull(`test`.`t`.`fin_date`) and isnull(`test`.`c`.`fin_date`
)) |
+-------+------+----------------------------------------------------------------

Gints Plivna
http://www.gplivna.eu