SQL for finding non-matching rows

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL for finding non-matching rows

Clark Christensen
I've seen it described here before, but I can't seem to
find it.

select a from t1 where a not in (select b from t2);

will find the resultset I'm looking for, and it seems OK
when the resulting in () list is small (maybe < 1000).  But
it seems less than ideal when the resulting list is large.
Isn't there a join I can do to accomplish the same a little
more elegantly?

Thanks!

 -Clark

Reply | Threaded
Open this post in threaded view
|

Re: SQL for finding non-matching rows

Kurt Welgehausen
> select a from t1 where a not in (select b from t2)

select a from t1 except select b from t2

or (SQLite v3 only)

select a from t1 where not exists
           (select b from t2 where b = a)

Which of these is fastest will probably depend on table
size and indexing; you'll have to try them out.


Regards
Reply | Threaded
Open this post in threaded view
|

Re: SQL for finding non-matching rows

John LeSueur
In reply to this post by Clark Christensen
Clark Christensen wrote:

>I've seen it described here before, but I can't seem to
>find it.
>
>select a from t1 where a not in (select b from t2);
>
>will find the resultset I'm looking for, and it seems OK
>when the resulting in () list is small (maybe < 1000).  But
>it seems less than ideal when the resulting list is large.
>Isn't there a join I can do to accomplish the same a little
>more elegantly?
>
>Thanks!
>
> -Clark
>
>  
>
select a
from t1
    left outer join
        t2 on
            t1.a = t2.b
where
    t2.b is null

John
Reply | Threaded
Open this post in threaded view
|

Re: SQL for finding non-matching rows

Clark Christensen
In reply to this post by Kurt Welgehausen
Terrific!  Thanks to all who replied.

 -Clark


--- Kurt Welgehausen <[hidden email]> wrote:

> > select a from t1 where a not in (select b from t2)
>
> select a from t1 except select b from t2
>
> or (SQLite v3 only)
>
> select a from t1 where not exists
>            (select b from t2 where b = a)
>
> Which of these is fastest will probably depend on table
> size and indexing; you'll have to try them out.
>
>
> Regards
>