mysql> create table t1 (row_id int unsigned not null auto_increment primary key, value int default null); Query OK, 0 rows affected (0.02 sec) mysql> create table t2 like t1; Query OK, 0 rows affected (0.02 sec) mysql> insert into t1 (value) values (1), (2), (NULL); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into t2 (value) values (1), (2), (NULL); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t1, t2; +--------+-------+--------+-------+ | row_id | value | row_id | value | +--------+-------+--------+-------+ | 1 | 1 | 1 | 1 | | 2 | 2 | 1 | 1 | | 3 | NULL | 1 | 1 | | 1 | 1 | 2 | 2 | | 2 | 2 | 2 | 2 | | 3 | NULL | 2 | 2 | | 1 | 1 | 3 | NULL | | 2 | 2 | 3 | NULL | | 3 | NULL | 3 | NULL | +--------+-------+--------+-------+ 9 rows in set (0.00 sec) mysql> select * from t1, t2 where t1.value = t2.value; +--------+-------+--------+-------+ | row_id | value | row_id | value | +--------+-------+--------+-------+ | 1 | 1 | 1 | 1 | | 2 | 2 | 2 | 2 | +--------+-------+--------+-------+ 2 rows in set (0.01 sec) mysql> select * from t1, t2 where t1.value = t2.value or t1.value is null or t2.value is null; +--------+-------+--------+-------+ | row_id | value | row_id | value | +--------+-------+--------+-------+ | 1 | 1 | 1 | 1 | | 3 | NULL | 1 | 1 | | 2 | 2 | 2 | 2 | | 3 | NULL | 2 | 2 | | 1 | 1 | 3 | NULL | | 2 | 2 | 3 | NULL | | 3 | NULL | 3 | NULL | +--------+-------+--------+-------+ 7 rows in set (0.00 sec) mysql> select * from t1; +--------+-------+ | row_id | value | +--------+-------+ | 1 | 1 | | 2 | 2 | | 3 | NULL | | 4 | NULL | +--------+-------+ 4 rows in set (0.00 sec) mysql> select * from t2; +--------+-------+ | row_id | value | +--------+-------+ | 1 | 1 | | 2 | 2 | | 3 | NULL | +--------+-------+ 3 rows in set (0.00 sec) mysql> select * from t1 left join t2 using (value); +-------+--------+--------+ | value | row_id | row_id | +-------+--------+--------+ | 1 | 1 | 1 | | 2 | 2 | 2 | | NULL | 3 | NULL | | NULL | 4 | NULL | +-------+--------+--------+ 4 rows in set (0.00 sec) mysql> select * from t1 right join t2 using (value); +-------+--------+--------+ | value | row_id | row_id | +-------+--------+--------+ | 1 | 1 | 1 | | 2 | 2 | 2 | | NULL | 3 | NULL | +-------+--------+--------+ 3 rows in set (0.00 sec)