JOINS on NULL vs WHERE on NULL
Assuming you're joining on a table with soft deletes, do the following queries do the same thing?
Is there a reason to prefer one over the other?
SELECT * FROM users
JOIN documents
ON documents.user_id = users.id AND documents.is_deleted IS NULL;
SELECT * FROM users
JOIN documents
ON documents.user_id = users.id
WHERE documents.is_deleted IS NULL;
Here's the sample schema:
CREATE TABLE users (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE documents (
`id` int NOT NULL AUTO_INCREMENT,
`user_id` varchar(255) DEFAULT NULL,
`doc` text,
`is_deleted` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY (`user_id`)
) ENGINE=InnoDB;
INSERT INTO users (id, name) VALUES (1, "bob");
INSERT INTO documents (id, user_id, doc, is_deleted) VALUES (1, 1, "doc 1", 1);
INSERT INTO documents (id, user_id, doc, is_deleted) VALUES (2, 1, "doc 2", null);
The explain plans are equivalent:
mysql> explain SELECT * FROM users
-> JOIN documents
-> ON documents.user_id = users.id AND documents.is_deleted IS NULL;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| 1 | SIMPLE | users | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | documents | NULL | ALL | user_id | NULL | NULL | NULL | 2 | 50.00 | Using where; Using join buffer (hash join) |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 3 warnings (0.00 sec)
mysql>
mysql> explain SELECT * FROM users
-> JOIN documents
-> ON documents.user_id = users.id
-> WHERE documents.is_deleted IS NULL;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| 1 | SIMPLE | users | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | documents | NULL | ALL | user_id | NULL | NULL | NULL | 2 | 50.00 | Using where; Using join buffer (hash join) |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 3 warnings (0.00 sec)