Firebird查询子查询条件不满足返回null，如何过滤无效行？

表结构 #

CREATE TABLE CLIENT_CODE_MASTER 
( 
    ID INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY, 
    CLIENT_CODE VARCHAR(100) UNIQUE NOT NULL, 
    CLIENT_ACTIVE BOOLEAN, 
    MAX_DD_LIMIT DECIMAL(20,2) DEFAULT 0 NOT NULL,
    DEALER_LOGIN_ID VARCHAR(50),
    DEALER_NAME VARCHAR(50)
);

CREATE TABLE TRADE_DATE_MASTER 
( 
    ID INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY , 
    TRADE_DATE DATE UNIQUE NOT NULL
);

CREATE TABLE FUTURES_DAILY_CLIENT_MTM 
(
    TRADE_DATE_ID INTEGER REFERENCES TRADE_DATE_MASTER(ID) ON UPDATE CASCADE,
    CLIENT_CODE_ID INTEGER REFERENCES CLIENT_CODE_MASTER(ID) ON UPDATE CASCADE,
    TURNOVER DECIMAL(20,2) DEFAULT 0 NOT NULL,
    MTOM DECIMAL(20,2) DEFAULT 0 NOT NULL,
    PRIMARY KEY (TRADE_DATE_ID, CLIENT_CODE_ID)
);

原查询 #

select 
    (select client_code from client_code_master ccm 
     where ccm.id = client_code_id and ccm.client_active = true) as client_code,
    (select trade_date from trade_date_master 
     where id = trade_date_id) as trade_date
from 
    futures_daily_client_mtm;

问题原因 #

你当前的写法是从FUTURES_DAILY_CLIENT_MTM取出所有行，仅通过子查询去获取对应客户端的编码。当客户端不满足client_active = true时，子查询返回null，但主查询的行不会被过滤，所以这些行依然会出现在结果里，只是client_code列显示null。

解决方法 #

改用INNER JOIN关联表，同时在关联条件里加入client_active = true，这样只有活跃客户端的行才会被保留：

select 
    ccm.client_code,
    tdm.trade_date
from 
    futures_daily_client_mtm fdcm
inner join client_code_master ccm 
    on fdcm.client_code_id = ccm.id and ccm.client_active = true
inner join trade_date_master tdm 
    on fdcm.trade_date_id = tdm.id;

由于外键约束已经保证TRADE_DATE_ID对应TRADE_DATE_MASTER的有效ID，所以用INNER JOIN关联日期表是安全的，不会丢失有效数据。

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