Adding WHERE clause gives not good result-CodePudding

My formula looks like that

SELECT EMP.*, DIM.GENDER
FROM Table1 as EMP
    JOIN Table2 as DIM 
        ON EMP.M_COD=DIM.M_COD
        AND EMP.ID=DIM.ID

It gives me result of 5,597,154. I know that I have 17 entries of certain category = 10 (added WHERE EMP.CAT_COD = 10) to the query, however when I try to get rid of IDs with category 10 (that's my criteria) then I add for instance WHERE EMP.CAT_COD <> 16 and I get 4,479,430 rows

Why it didn't just remove these 17 rows and how to properly exclude them from the result?

CodePudding user response：

The <> is not NULL safe and all 16/NULL are removed from final resultset. In order to filter only 16 IS DISTINCT FROM should be used:

SELECT EMP.*, DIM.GENDER
FROM Table1 as EMP
JOIN Table2 as DIM 
  ON EMP.M_COD=DIM.M_COD
 AND EMP.ID=DIM.ID
WHERE EMP.CAT_COD IS DISTINCT FROM 16;
-- EMP.CAT_COD IS NOT DISTINCT FROM 16 -- NULL safe equality operator

CodePudding user response：

Is it because you use 16 instead of 10? You mentioned the following filter:

WHERE EMP.CAT_COD = 10

then you said you use this filter:

WHERE EMP.CAT_COD <> 16

How many rows return when you run the following one? :)

SELECT EMP.*, DIM.GENDER
FROM Table1 as EMP
JOIN Table2 as DIM 
  ON EMP.M_COD=DIM.M_COD
 AND EMP.ID=DIM.ID
WHERE EMP.CAT_COD IS DISTINCT FROM 10;