Firebird多字段NOT IN用法修正：查询可购买股份的SQL语句问题

涉及业务表说明 #

• ShareNumber表（股份基础信息表）：字段包含ShareTypeCode（股份类型编码）、ShareNumber（股份编号）、StartDate（生效日期）、LastDate（失效日期）；有效规则为LastDate为空代表有效，其中stc1类型下snA已作废，sn001-sn005为有效股份，stc2类型下sn001-sn005均为有效股份。
• ShareHolding表（股东持股表）：字段包含ShareHolderCode（股东编码）、ShareTypeCode（股份类型编码）、ShareNumber（股份编号）、StartDate（持股起始日期）、LastDate（持股结束日期）；有效持有规则为LastDate为空代表当前仍被持有，其中stc1类型下sn001已被股东退回公司，sn002当前处于被持有状态。

需求说明 #

投资者拟购买股份类型编码为stc1、股份编号为sn002的股份，需要校验该股份同时满足状态有效、未被当前持有两个条件，查询有返回结果则代表该股份可售。

原SQL问题 #

原SQL使用多字段NOT IN的语法不符合Firebird数据库的语法支持要求，原SQL如下：

SELECT "SN"."ShareTypeCode", "SN"."ShareNumber" 
FROM "ShareNumber" "SN" 
WHERE "SN"."LastDate" is null 
and "SN"."ShareTypeCode" = 'stc1' 
and "SN"."ShareNumber" = 'sn002' 
and ("SN"."ShareTypeCode", "SN"."ShareNumber") not in ( select "SH"."ShareTypeCode", "SH"."ShareNumber" from "ShareHolding" "SH" where "SH"."LastDate" is null)

修正后符合Firebird语法的SQL #

Firebird不支持多列IN/NOT IN语法，可以用NOT EXISTS替代实现等价逻辑，修正后语句如下：

SELECT "SN"."ShareTypeCode", "SN"."ShareNumber"
FROM "ShareNumber" "SN"
WHERE "SN"."LastDate" IS NULL
  AND "SN"."ShareTypeCode" = 'stc1'
  AND "SN"."ShareNumber" = 'sn002'
  AND NOT EXISTS (
    SELECT 1 FROM "ShareHolding" "SH"
    WHERE "SH"."LastDate" IS NULL
      AND "SH"."ShareTypeCode" = "SN"."ShareTypeCode"
      AND "SH"."ShareNumber" = "SN"."ShareNumber"
)

该语句逻辑和原SQL完全等价，按照给出的测试数据，stc1类型的sn002当前处于被持有状态，执行该SQL不会返回结果，代表该股份不可售。

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