Обсуждение: RE: [HACKERS] Subselects and NOTs
Exactly the same with the latest Oracle7 version. Michael -- Dr. Michael Meskes, Project-Manager | topsystem Systemhaus GmbH meskes@topsystem.de | Europark A2, Adenauerstr. 20 meskes@debian.org | 52146 Wuerselen Go SF49ers! Go Rhein Fire! | Tel: (+49) 2405/4670-44 Use Debian GNU/Linux! | Fax: (+49) 2405/4670-10 > -----Original Message----- > From: Zeugswetter Andreas SARZ [SMTP:Andreas.Zeugswetter@telecom.at] > Sent: Wednesday, February 18, 1998 6:33 PM > To: 'pgsql-hackers@hub.org' > Subject: Re: [HACKERS] Subselects and NOTs > > This is what I did: > create table a (a int, a1 char(8)); > create table b (b int); > insert into a values (1, 'one'); > insert into a values (NULL, 'null'); > > > and this is what I got from Informix: > > select * from a where a not in (select * from b); > a a1 > 1 one > null > 2 row(s) retrieved. > > select * from a where not (a in (select * from b)); > a a1 > 1 one > null > 2 row(s) retrieved. > > select * from a where not a in (select * from b); > a a1 > 1 one > null > 2 row(s) retrieved. > > select * from a where a<>(select * from b); > a a1 > No rows found. > > select * from a where a=(select * from b); > a a1 > No rows found. > > Andreas
Meskes, Michael wrote: > > Exactly the same with the latest Oracle7 version. I also get the same results with Sybasem, with one interesting exception. Sybase will not let me select * in a subquery! It gives me the following: The symbol '*' can only be used for a subquery select list when the subquery is introduced with EXISTS or NOT EXISTS. changing this to "select b from b" seems to work though. Ocie > > Michael > > -- > Dr. Michael Meskes, Project-Manager | topsystem Systemhaus GmbH > meskes@topsystem.de | Europark A2, Adenauerstr. 20 > meskes@debian.org | 52146 Wuerselen > Go SF49ers! Go Rhein Fire! | Tel: (+49) 2405/4670-44 > Use Debian GNU/Linux! | Fax: (+49) 2405/4670-10 > > > -----Original Message----- > > From: Zeugswetter Andreas SARZ [SMTP:Andreas.Zeugswetter@telecom.at] > > Sent: Wednesday, February 18, 1998 6:33 PM > > To: 'pgsql-hackers@hub.org' > > Subject: Re: [HACKERS] Subselects and NOTs > > > > This is what I did: > > create table a (a int, a1 char(8)); > > create table b (b int); > > insert into a values (1, 'one'); > > insert into a values (NULL, 'null'); > > > > > > and this is what I got from Informix: > > > select * from a where a not in (select * from b); > > a a1 > > 1 one > > null > > 2 row(s) retrieved. > > > select * from a where not (a in (select * from b)); > > a a1 > > 1 one > > null > > 2 row(s) retrieved. > > > select * from a where not a in (select * from b); > > a a1 > > 1 one > > null > > 2 row(s) retrieved. > > > select * from a where a<>(select * from b); > > a a1 > > No rows found. > > > select * from a where a=(select * from b); > > a a1 > > No rows found. > > > > Andreas > >
ocie@paracel.com wrote: > > Meskes, Michael wrote: > > > > Exactly the same with the latest Oracle7 version. > > I also get the same results with Sybasem, with one interesting > exception. Sybase will not let me select * in a subquery! It gives > me the following: > > The symbol '*' can only be used for a subquery select list when the subquery is > introduced with EXISTS or NOT EXISTS. It's funny... SyBases could check subquery' target list length... Ok, thanks. Pg returns the same results for these queries. But I asked Andreas to run other queries and they show that both Oracle & Informix push NOT into subquery clause (and so, 'NOT x IN' is equal 'x NOT IN'), Pg doesn't, currently. Ocie, could you run this in SyBase: create table a (a int, a1 char(8)); create table b (b int); insert into a values (1, 'one'); insert into a values (NULL, 'null'); insert into b values (1); insert into b values (NULL); select * from a where a in (select * from b); -- 1 row with a == 1 expected select * from a where a not in (select * from b); -- 0 row expected select * from a where not a in (select * from b); -- 0 row in Oracle & Informix, 1 row in Pg (with a == NULL), SyBase ??? select * from a where not (a in (select * from b)); -- the same. SyBase ??? Vadim