SQL обрабатывает нулевые внешние ключи в предложении Where

Допустим, у меня есть таблица с именем my_table с primary_key table_pk и тремя nullible полями (внешними ключами) с именами X_fk, Y_fk и Z_fk соответственно, и еще одно поле с именем data. Таблицы X, Y и Z все поля первичного ключа и поле имя (например, {X_pk, X_name}, {Y_pk, Y_name}, {Z_pk, Z_name}).

Мне нужен запрос, который однозначно возвращает строку из my_table, заданную именами в таблицах X, Y и Z. 

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. Как я могу изменить приведенный выше запрос, чтобы извлечь уникальную запись из таблицы, учитывающей уникальные записи, где некоторые из внешних ключей равны нулю?

2 3
sql sql-server sql-server-2008-r2

2 ответа:

Попробуйте это:

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

Тот же ответ, что и у LukLed, но с входами, связанными только один раз с переднего конца 

SELECT table_pk 
FROM my_table
cross join (select ? iX, ? iY, ? iZ) i
WHERE 
    ((i.iX is null and X_fk is null) OR X_fk = (SELECT X_pk FROM X WHERE X_name = i.iX))
  AND 
    ((i.iY is null and Y_fk is null) or Y_fk = (SELECT Y_pk FROM Y WHERE Y_name = i.iY))
  AND 
    ((i.iZ is null and Z_fk is null) or Z_fk = (SELECT Z_pk FROM Z WHERE Z_name = i.iZ))

Это выглядит короче, но не приносит индексам никакой пользы (предполагая, что -1 не является допустимым значением).

SELECT table_pk 
FROM my_table
WHERE 
    Isnull(X_fk,-1) = isnull((SELECT X_pk FROM X WHERE X_name = ?),-1)
  AND 
    Isnull(Y_fk,-1) = Isnull((SELECT Y_pk FROM Y WHERE Y_name = ?),-1)
  AND 
    Isnull(Z_fk,-1) = Isnull((SELECT Z_pk FROM Z WHERE Z_name = ?),-1)
2