CODEWITHSUNDEEP
sqlinformix
famedoro
famedoro

Reputation: 1241

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?

Upvotes: 1

Views: 222

Answers (2)

cproinger
cproinger

Reputation: 2288

i don't think there is a better way than yours. i can give you a different one, but ultimately this will likely be translated to the same operations from the query-engine. you query actually might be more performant. 

select artind.* from artind 
    left join coord on key_code = cm_key_coord_code and cm_t_coor = 2 
where cm_key_coord_code is null

if you have performance problems with yours i suggest looking at the indexes on the tables

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270301

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
                 );

or:

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;

Upvotes: 3

Related Questions