Re: LEFT OUTER JOIN on NULL value

[Martin Wright]

Hi Rick,

The EXPLAIN's are not identical but it's not clear to me which is better:

EXPLAIN SELECT LEFT JOIN (join field is actually NULL)

+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | Messages | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
| 1 | SIMPLE | Members | const | PRIMARY | NULL | NULL | NULL | 1 | NULL |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+


EXPLAIN SELECT ISNULL (join field is actually NULL)

+----+--------------------+----------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+----------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+
| 1 | PRIMARY | Messages | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
| 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+--------------------+----------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+

EXPLAIN SELECT LEFT JOIN (join field links to 1 record)

+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | Messages | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
| 1 | SIMPLE | Members | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+

EXPLAIN SELECT ISNULL (join field links to 1 record)

+----+--------------------+----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+----------+-------+---------------+---------+---------+-------+------+-------+
| 1 | PRIMARY | Messages | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
| 2 | DEPENDENT SUBQUERY | Members | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
+----+--------------------+----------+-------+---------------+---------+---------+-------+------+-------+

I also did the "SHOW STATUS" commands you suggested and they were the same for both types of query. Specifically, "Handler_commit" increased by one for each query, "Handler_external_lock" increased by four for each query, and "Handler_read_key" increased by one in both cases when the join field was NULL and it increased by two in both cases when the join field linked to a record.