SQL – Select using attributes from two tables

I am having an issue in extracting data using data of two tables in SQL.

select A, B, C, D
from Table_one T1
where A in (select T2.A from Table_two T2
where T2.E <> 'ZZZ');

This returns A, B, C, D where E in T2 is not ZZZ. However, when I add another where clause like below, it returns data where T2 is ZZZ also.

select A, B, C, D
from Table_one T1
where A in (select T2.A from Table_two T2
where T2.E <> 'ZZZ')
and D <> 0 ;

This ignores “T2.E <> ‘ZZZ'” part, but “D<>0” is not ignored. Why is this happening?

Answer

Because you have duplicates in Table_two. For some of those duplicates, one has the value of ZZZ and the other does not.

You are using the wrong logic if you want to exclude rows that have a ZZZ in table_two. I would recommend NOT EXISTS:

select A, B, C, D
from Table_one T1
where not exists (select 1
                  from Table_two T2
                  where T1.A = T2.A and
                        T2.E = 'ZZZ'
                 ) and
      D <> 0 ;