How can I join 2 tables and match the null values

Question

I have these tables:

mysql> select bookId, productId, clusterId, symbolId, shares from SymbolAimOrderStats where bookId = "BFM";
+--------+-----------+-----------+----------+--------+
| bookId | productId | clusterId | symbolId | shares |
+--------+-----------+-----------+----------+--------+
| BFM    | NULL      | NULL      | NULL     |    522 |
| BFM    | BFM       | NULL      | NULL     |    522 | 
| BFM    | BFM       | BFM       | NULL     |    522 | 
| BFM    | BFM       | BFM       | 0000f52a |    506 | 
| BFM    | BFM       | BFM       | 00010a4d |      2 | 
| BFM    | BFM       | BFM       | 00010a7c |      1 | 
| BFM    | BFM       | BFM       | 00010a89 |      3 | 
| BFM    | BFM       | BFM       | 00010ba3 |      3 | 
| BFM    | BFM       | BFM       | 00013c7e |      3 | 
| BFM    | BFM       | BFM       | 00013c97 |      4 | 
+--------+-----------+-----------+----------+--------+
10 rows in set (0.00 sec)

mysql> select bookId, productId, clusterId, symbolId, shares from SymbolOrderStats where bookId = "BFM";
+--------+-----------+-----------+----------+--------+
| bookId | productId | clusterId | symbolId | shares |
+--------+-----------+-----------+----------+--------+
| BFM    | NULL      | NULL      | NULL     |    393 | 
| BFM    | BFM       | NULL      | NULL     |    393 | 
| BFM    | BFM       | BFM       | NULL     |    393 | 
| BFM    | BFM       | BFM       | 0000f52a |    377 | 
| BFM    | BFM       | BFM       | 00010a4d |      2 | 
| BFM    | BFM       | BFM       | 00010a7c |      1 | 
| BFM    | BFM       | BFM       | 00010a89 |      3 | 
| BFM    | BFM       | BFM       | 00010ba3 |      3 | 
| BFM    | BFM       | BFM       | 00013c7e |      3 | 
| BFM    | BFM       | BFM       | 00013c97 |      4 | 
+--------+-----------+-----------+----------+--------+
10 rows in set (0.00 sec)

I want to join these tables and get the shares column from both, including the rows that have NULLs.

I know that you cannot match NULLs, so this of course does not give me my desired result:

mysql> select a.bookId, a.productId, a.clusterId, a.symbolId, a.shares, o.shares 
from SymbolAimOrderStats a 
left join SymbolOrderStats o 
on a.bookId = o.bookId and a.productId = o.productId and 
a.clusterId = o.clusterId and a.symbolId = o.symbolId where a.bookId = "BFM";

So I've tried this:

mysql> select a.bookId, a.productId, a.clusterId, a.symbolId, a.shares, o.shares 
from SymbolAimOrderStats a 
left join SymbolOrderStats o 
on a.bookId = o.bookId and 
(a.productId = o.productId or (a.productId is null and o.productId is null)) and 
(a.clusterId = o.clusterId or (a.clusterId is null and o.clusterId is null)) and
(a.symbolId = o.symbolId or (a.symbolId is null or a.symbolId is null)) 
where a.bookId = "BFM";

But that also does not give what I want. Can someone please tell me how I can get the result set I want?

Here is what I want returned:

+--------+-----------+-----------+----------+--------+--------+
| bookId | productId | clusterId | symbolId | shares | shares |
+--------+-----------+-----------+----------+--------+--------+
| BFM    | NULL      | NULL      | NULL     |    522 |    393 |
| BFM    | BFM       | NULL      | NULL     |    522 |    393 | 
| BFM    | BFM       | BFM       | NULL     |    522 |    393 | 
| BFM    | BFM       | BFM       | 0000f52a |    506 |    377 | 
| BFM    | BFM       | BFM       | 00010a4d |      2 |      2 | 
| BFM    | BFM       | BFM       | 00010a7c |      1 |      1 | 
| BFM    | BFM       | BFM       | 00010a89 |      3 |      3 | 
| BFM    | BFM       | BFM       | 00010ba3 |      3 |      3 | 
| BFM    | BFM       | BFM       | 00013c7e |      3 |      3 | 
| BFM    | BFM       | BFM       | 00013c97 |      4 |      4 | 
+--------+-----------+-----------+----------+--------+--------+
10 rows in set (0.00 sec)

This is in MySQL.

Answer 1

If you need a NULL to be meaningful, change it to something meaningful.

select  a.bookId, a.productId, a.clusterId, a.symbolId, a.shares, o.shares 
from    SymbolAimOrderStats a 
left join SymbolOrderStats o 
  on    a.bookId = o.bookId
   and ifnull( a.productId, @dummy ) = ifnull( o.productId, @dummy )
   and ifnull( a.clusterId, @dummy ) = ifnull( o.clusterId, @dummy )
   and ifnull( a.symbolId, @dummy ) = ifnull( o.symbolId, @dummy ) 
where a.bookId = "BFM";

Where @dummy is anything as long as it doesn't conflict with non-null values to eliminate the possibility of matching a NULL with a non-NULL and is the same data type as the field. If productId, clusterId and symbolId are different types, you'll need three dummy variables. Or you can hardcode values there, but that's not a good practice.

However, this raises the question that if the NULL values in your tables actually mean something, why aren't they already set to those values? I don't mean to be critical -- there could be perfectly reasonable reasons they have to remain NULL. But maybe you could be overlooking an opportunity to improve your data.

Answer 2

Perhaps the last condition on the NULL values should use and instead of or:

select a.bookId, a.productId, a.clusterId, a.symbolId, a.shares, o.shares 
from SymbolAimOrderStats a 
left join SymbolOrderStats o 
on a.bookId = o.bookId and 
(a.productId = o.productId or (a.productId is null and o.productId is null)) and 
(a.clusterId = o.clusterId or (a.clusterId is null and o.clusterId is null)) and
(a.symbolId = o.symbolId or (a.symbolId is null and o.symbolId is null)) 
------------------------------------------------^ --^
where a.bookId = "BFM";