SQL 在 Where 子句中处理可为空的外键

Question

假设我有一个名为 my_table 的表，其中包含 primary_key table_pk 和三个可空字段(外键)，分别名为 X_fk、Y_fk 和 Z_fk，还有一个名为 data 的字段。表 X、Y 和 Z 都有一个主键字段和一个名称字段(例如 {X_pk, X_name}, {Y_pk, Y_name}, {Z_pk, Z_name})。

我想要一个查询，它在给定表 X、Y 和 Z 中的名称的情况下，从 my_table 中唯一地返回一行。

SELECT table_pk 
FROM my_table 
WHERE 
    X_fk = (SELECT X_pk FROM X WHERE X_name = ?) 
  AND 
    Y_fk = (SELECT Y_pk FROM Y WHERE Y_name = ?) 
  AND 
    Z_fk = (SELECT Z_pk FROM Z WHERE Z_name = ?)

虽然当我想查找行 (X_Name, Y_Name, Z_Name) = ('XXX', 'YYY', NULL) 时这不起作用，因为 Z_fk = NULL 永远不会导致 TRUE。我如何修改上述查询以从表中提取一个唯一条目来计算其中一些外键为 NULL 的唯一条目？

Answer 1

试试这个:

SELECT min(table_pk)
FROM my_table 
WHERE 
    ((X_fk = (SELECT X_pk FROM X WHERE X_name = ?)) OR (? IS NULL AND X_fk IS NULL))
AND 
    ((Y_fk = (SELECT Y_pk FROM Y WHERE Y_name = ?)) OR (? IS NULL AND Y_fk IS NULL)) 
AND 
    ((Z_fk = (SELECT Z_pk FROM Z WHERE Z_name = ?)) OR (? IS NULL AND Z_fk IS NULL))