Difficult mysql query? Please help

Anon

I have 2 tables that are similar. What i would like to do a query and output only the rows that dont exists in the other table.

Thanks alot

Tom

Anon

select
from table1 t1
where t1.field NOT IN (Select field2 from table2)
UNION
select
from table2 t2
where t2.field NOT IN (Select field1 from table1)

where 'field' is the field(s) that is/are simular.
UNION assumes the fields returned from * are the same in table1 and table2. If they are not, then select only rows that are simular.
If UNION doesn't work do each query seperate.

This would work in Oralce, not positive about other db's

Anon

Do this:

select table1.*
from table1 left join table2 on table1.field = table2.field
where table2.field is null

This will return all of the fields from table1 that don't exist in table2.

Anon

Hello!

I was passing by and noticed this one. Carefull with the given example. Oracle limits "in ()" to 255 entries. So if the count(*) over table 1 or 2 returns more than 255 rows, this query would break.

I would do it like this (don't know if it works outside of oracle).

select
from table1 t1
where NOT exists (Select 1
from table2 t2
t2.field2 = t1.field1)
UNION
select
from table2 t2
where NOT exists (Select 1
from table1 t1
t1.field1 = t2.field2)

Result is the same, not limited by 255 entris in IN. I think it will cost less (it is optimized), as the fullscan on the IN is no longer done.

Hope it helps!

Duarte Loreto

Anon

I was just looking for a post I made and found this on when I searched and thought I would clarify the statement:

Yes IN does restrict to 255 items when they are specified directly like IN ('1','2','255')

but if you are using the IN with a subquery
IN (select * from table) then the 255 does not come into play. The table can return more than 255 rows and the query will still work. YOu will have to determine based on indexes and the such which method (EXISTS or IN) should be used. Both have their purposes.