Thread: stupid select question...
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 something like this with a select url from table1 where a.url not in select url from table b. Can I do this in postgres? Konstantin -- Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: elwood@agouros.de Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185 ---------------------------------------------------------------------------- "Captain, this ship will not sustain the forming of the cosmos." B'Elana Torres
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.Elphick@lfix.co.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
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.Elphick@lfix.co.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 >