Not equals operator not working in SQL Select statement

Question

I have two tables A and B that contains the same Id (is not a primary key in the two tables) , and I would like to get only the data that exist in Table A .

Table A:

Table B

Query statement

select distinct o.Id
from  Table A o,Table B ot
where o.isActive=1 and not(ot.Id=o.Id)

This will return the same data that exists in Table A, but the expected result should be :

How can I get this works?

I don't think you want a cartesian product. Try `SELECT DISTINCT o.Id FROM TableA WHERE o.IsActive = 1 AND o.Id NOT IN (SELECT ot.Id FROM TableB ot);` — Dan Guzman, Mar 26 '21 at 16:50

score 1 · Accepted Answer · answered Mar 26 '21 at 16:50

Firstly, you shouldn't use , join syntax, it was deprecated 30 years ago. Your current query is the same as this:

select distinct o.Id
from  TableA o
join TableB ot on o.isActive=1 and ot.Id <> o.Id

This will return all rows from TableA where there exist rows that do not match this one. This is not what you are trying to do. Instead, use EXCEPT:

select o.Id
    from TableA o
    where o.isActive=1
except
select ot.Id
    from Table B ot;

EXCEPT implies a DISTINCT. If you would like all rows from TableA including duplicates then you need NOT EXISTS:

select o.Id
from TableA o
where o.isActive=1
    and not exists (select 1
        from Table B ot
        where ot.Id = oId);

I have tried your solution before posting the question but it does not to be working when the number of columns is not the same when comparing the statement before `except` and the statement after — , Mar 26 '21 at 16:56

score 0 · Answer 2 · edited Mar 26 '21 at 17:23

0

Below is one approach. I also added a condition to ensure there is a legit value in the Id field in Table B since you said it's not an identity column.

select distinct Id
from Table A
where Id not in
    (select Id
    from Table B
    where Id > 0)

edited Mar 26 '21 at 17:23

Charlieface

52,284
6
19
43

answered Mar 26 '21 at 17:00

GroovyNorCalGal

1
2

1

Please note that `not in` has issues dealing with nullable columns and will not return correct results https://stackoverflow.com/questions/173041/not-in-vs-not-exists – Charlieface Mar 26 '21 at 17:23
That's why I included where id > 0. – GroovyNorCalGal Mar 26 '21 at 19:26

score 0 · Answer 3 · answered May 10 '23 at 19:08

You can use not exists:

select distinct o.Id from [Table A] o where o.isActive=1 and not exists (select 1 from [Table B] ot where o.Id =ot.Id) This will select unique Id from [Table A] which doesn't exist in [Table B]. If Id column doesn't have duplicate value in [Table A] then you an remove ditinct from the query.

Not equals operator not working in SQL Select statement

3 Answers3