mysql - 如何连接 2 个表并匹配空值

Question

我有这些表:

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)

我想连接这些表并从两个表中获取共享列，包括具有 NULL 的行。

我知道你不能匹配 NULL，所以这当然不会给我我想要的结果:

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";

所以我试过这个:

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";

但这也不能满足我的要求。有人可以告诉我如何获得我想要的结果集吗？

这是我要返回的内容:

+--------+-----------+-----------+----------+--------+--------+
| 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)

这是在 MySQL 中。

Answer 1

也许 NULL 值的最后一个条件应该使用 and 而不是 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";