Why does my query return null when subquery condition is false

Question

I have these three tables in my Firebird database:

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)
);

Now when running this query:

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;

The column 'client_code' is showing [null] where the condition ccm.client_active = true is not satisfied.

These rows should not be in the result.

Why is this happening? How do I fix it?

Answer 1

When you use a singleton select like that as an expression in the column list of another select, it will produce NULL when that singleton select produces no row. Your select statement basically says, for each row produced by the from, execute two other queries and show their result. The fact one of those queries produced no result doesn't mean it should suddenly exclude that row, instead it will display NULL in that column.

If you want to excluded rows where the result of the subquery is NULL, you need to explicitly exclude it (e.g. by making it a derived table, and then excluding rows by using where client_code is not null), but better, as user13964273 mentioned in the comments, is to solve this by using an INNER JOIN instead:

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