NOT IN will not work if u have nulls in the list.
Anand
On Sun, Jan 14, 2001 at 01:25:59PM +0000, Oliver Elphick wrote:
>Konstantinos Agouros wrote:
> >
> >Hi,
> >
> >my sql has gotten a little rusty, here's what I want to do:
> >I have two tables both contain urls. How what I want are all the entries in
> >table b where there is no matching url in table a. I do remember doing somet
> >hing
> >like this with a
> >select url from table1 where a.url not in select url from table b.
> >
> >Can I do this in postgres?
>
>SELECT url FROM a WHERE url NOT IN (select url FROM b);
>
>but watch out for nulls:
>
>bray=# select * from a;
> url
>------------
> http:fred1
> http:fred2
>(2 rows)
>
>bray=# select * from b;
> id | url
>----+------------
> 1 | http:fred1
> 2 | <-- url is null
>(2 rows)
>
>bray=# SELECT url FROM a WHERE url NOT IN (select url FROM b);
> url
>-----
>(0 rows)
>
>bray=# SELECT url FROM a WHERE url NOT IN (select url FROM b WHERE NOT url IS
>NULL);
> url
>------------
> http:fred2
>(1 row)
>
>--
>Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
>Isle of Wight http://www.lfix.co.uk/oliver
>PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
>GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
> ========================================
> "If we confess our sins, he is faithful and just to
> forgive us our sins, and to cleanse us from all
> unrighteousness." I John 1:9
>