如何处理WHERE子句中的NULL值，并根据遇到的情况更改目标列

Question

我需要WHERE子句来更改遇到NULL时它正在评估的列。例如，我想做这样的事情:

SELECT *
FROM customer c
WHERE CASE WHEN c.cust_id_1(@variable) IS NOT NULL THEN c.cust_id_1 = @variable
ELSE CASE WHEN c.cust_id_2(@variable) IS NOT NULL THEN c.cust_id_2 = @variable
ELSE c.cust_id_3 = @variable

这样的事情可能吗?3个cust_id中不会有一个为NULL。

Answer 1

这似乎不太理想的表设计，但不是一个简单的COALESCE，你之后?

WHERE @variable = COALESCE(cust_id_1, cust_id_2, cust_id_3);

Answer 2

这里不需要CASE表达式，只需要逻辑运算符

SELECT *
FROM customer c
WHERE
(c.cust_id_1 IS NOT NULL AND c.cust_id_1 = @variable)
OR
(c.cust_id_2 IS NOT NULL AND c.cust_id_2 = @variable)
OR
(c.cust_id_3 IS NOT NULL AND c.cust_id_3 = @variable)