Compare 2 Tables ( <> Operation )

[aas1611]

Hi,

I have a small problem in comparing two tables, that has to return records that are not in the other table.

Suppose I have the following tables:

T1: T2:
Field1 Field1
------ -------
1 b
2 a
d c
a 1
x
z

And, this is the code:

select T1.Field1 from T1, T2
where T1.Field1 <> T2.Field1
group by T1.Field1

I was expecting the result of 2, d, x, z (which are not in T2). Instead, I got all 6 records from T1. I've tried other operation like !=, NOT IN, but came up the same. The Field1 on T1 is primary key.

Any clue, anyone?

 

[nego78]

SELECT T1.Field1 FROM T1 LEFT JOIN T2 ON T1.Field1 = T2.Field2 WHERE T2.Field2 IS NULL

Via-Net - web directory

 

[swampBoogie]

NOT in should work

select column1 
from T1
where column1 not in
  (select column1 
     from T2)

unless you have an old Mysql version.

select t1.column1 
from T1 left join t2
on t1.column1 = t2.column1
where t2.column1 is null

I prefer the not in version being more intuitive and easier to understand.