SQL查询实现：筛选历史中从未处于State=2状态的设备

现有2张表，结构及样例数据如下：

Device表 #

+----------+----------+
| DeviceId | Name     |
+----------+----------+
|        10| Dev10    |
|        20| Dev20    |
|        30| Dev30    |
+----------+----------+

History表 #

+----------+-------+---------------------+
| DeviceId | State |      TimeStamp      |
+----------+-------+---------------------+
|        10|     0 | 06/09/2021 11:10:01 |
|        20|     1 | 06/09/2021 11:10:02 |
|        30|     0 | 06/09/2021 11:10:03 |
|        10|     0 | 06/09/2021 11:10:04 |
|        20|     0 | 06/09/2021 11:10:05 |
|        30|     1 | 06/09/2021 11:10:05 |
|        10|     2 | 06/09/2021 11:10:06 |
|        20|     0 | 06/09/2021 11:10:06 |
|        30|     0 | 06/09/2021 11:10:06 |
+----------+-------+---------------------+

已知可以通过以下SQL查询出所有曾经处于State=2状态的设备（已修正原SQL中字段名不匹配的问题）：

SELECT *
FROM Device A
CROSS APPLY (SELECT TOP 1 * 
             FROM History B
             WHERE A.DeviceId = B.DeviceId and B.[State]=2
             ORDER BY B.TimeStamp DESC) as B
order by A.DeviceId

需求实现：查询从未处于State=2状态的设备 #

方案1：使用NOT EXISTS（兼容性最好、执行效率高） #

SELECT A.DeviceId, A.Name
FROM Device A
WHERE NOT EXISTS (
    SELECT 1 
    FROM History B 
    WHERE B.DeviceId = A.DeviceId AND B.State = 2
)
ORDER BY A.DeviceId

方案2：沿用APPLY语法（和原有查询逻辑风格统一） #

用OUTER APPLY替代CROSS APPLY，筛选匹配结果为空的记录即可：

SELECT A.DeviceId, A.Name
FROM Device A
OUTER APPLY (
    SELECT TOP 1 1 flag
    FROM History B
    WHERE A.DeviceId = B.DeviceId AND B.[State] = 2
    ORDER BY B.TimeStamp DESC
) AS B
WHERE B.flag IS NULL
ORDER BY A.DeviceId

两种方案执行后返回结果都和预期一致：

+----------+----------+
| DeviceId | Name     |
+----------+----------+
|        20| Dev20    |
|        30| Dev30    |
+----------+----------+

内容的提问来源于stack exchange，提问作者amplifier