MYSQL查询处理一对值中的空值

Question

假设我们有以下数据库:

First_Id    Second_Id     Name
null        1             Durant
null        1             Kobe
1           2             Lebron
2           2             Dwight
1           3             Dirk

我们希望以两列组成某些特定对的方式提取行，例如:

(1,2),(1,3)

所以答案将等于:

First_Id    Second_Id     Name
1           2             Lebron
1           3             Dirk

这可以使用以下查询:

SELECT *
FROM PLAYERS
WHERE (First_Id, Second_Id) IN (VALUES (1,2), (1,3))

我想要做的是找到那些 First_Id 为 null 的行，我该怎么做？

SELECT *
FROM PLAYERS
WHERE (First_Id, Second_Id) IN (VALUES (null,1),(1,2), (1,3))

所需的输出是:

First_Id    Second_Id     Name
null        1             Durant
null        1             Kobe
1           2             Lebron
1           3             Dirk

Answer 1

为什么不直接使用等于和逻辑运算符。在具体情况下，它比您想要做的更简单:

SELECT *
FROM PLAYERS
WHERE (First_Id is null and Second_Id = 1) 
   OR (First_Id = 1 and Second_Id = 2)
   OR (First_Id = 1 and Second_Id = 3)