famedoro famedoro - 1 year ago 58
SQL Question

SQL: What is the best way to select rows from a table that do not exist in another table only with a particular id?

I use the Informix database where I have 2 tables artind and coord that have relationship with one another, key_code and cm_key_coord_code

table artind

| Field | Type |
| key_code | char(8) |
| descr | char(30) |

table coord

| Field | Type |
| cm_key_coord_code | char(8) |
| cm_t_coor | int |
| descr_coord | char(30) |

Usually to select all record in table artind that not have a record with
same code ( key_code equal to cm_key_coord_code ) and cm_t_coor = 2 in
table coord I use:

select * from artind where
key_code not in (select cm_key_coord_code from coord
where cm_t_coor = 2);

There is a better way?

Answer Source

Your method is fine, but not recommended. If any cm_key_coord_code values are NULL, then no records will be selected. That is how NOT IN is defined, but not usually what is intended.

I advise either NOT EXISTS or LEFT JOIN:

select a.*
from artind a
where not exists (select 1
                  from coord c
                  where c.cm_t_coor = 2 and c.cm_key_coord_code = a.key_code


select a.*
from artind a left join
     coord c
     on c.cm_t_coor = 2 and c.cm_key_coord_code = a.key_code
where c.cm_key_coord_code is null;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download