RE: [sqlite] Number of elements in IN clause
I don't have PostrgreSQL installed, but the docs say IN requires a subquery. http://www.postgresql.org/docs/8.2/interactive/functions-subquery.html MySQL does not support this syntax either (that one I tested). Sam --- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, January 28, 2008 9:20 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Number of elements in IN clause "Samuel R. Neff" <[EMAIL PROTECTED]> wrote: > I don't think it is standard SQL. At the very least, it doesn't work in > MSSQL. Standard is > > SELECT * FROM maintable WHERE key IN (select x from stuff); > > SQLite shortened version is much nicer.. wish it was standard. > SQLite also accepts the more verbose version shown above, of course. The two statements do *exactly* the same thing. I cannot believe that I would have put in the short-hand notation without having seen it somewhere else first. Does PostgreSQL support the short-hand version? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Number of elements in IN clause
Just experimented with the short-hand version. It seems to work only when the "stuff" table has a single field, although the field name doesn't matter. More than one field causes a query error, even if the field name being compared is in the table. I've actually never seen this notation, but it does make sense. It would be nice if it matched on field name, that way the same syntax could be used in both cases. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, January 28, 2008 9:20 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Number of elements in IN clause "Samuel R. Neff" <[EMAIL PROTECTED]> wrote: > I don't think it is standard SQL. At the very least, it doesn't work > in MSSQL. Standard is > > SELECT * FROM maintable WHERE key IN (select x from stuff); > > SQLite shortened version is much nicer.. wish it was standard. > SQLite also accepts the more verbose version shown above, of course. The two statements do *exactly* the same thing. I cannot believe that I would have put in the short-hand notation without having seen it somewhere else first. Does PostgreSQL support the short-hand version? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Number of elements in IN clause
"Samuel R. Neff" <[EMAIL PROTECTED]> wrote: > I don't think it is standard SQL. At the very least, it doesn't work in > MSSQL. Standard is > > SELECT * FROM maintable WHERE key IN (select x from stuff); > > SQLite shortened version is much nicer.. wish it was standard. > SQLite also accepts the more verbose version shown above, of course. The two statements do *exactly* the same thing. I cannot believe that I would have put in the short-hand notation without having seen it somewhere else first. Does PostgreSQL support the short-hand version? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Number of elements in IN clause
Yes, this is standard SQL. The syntax is:
SELECT * from maintable where key IN (SELECT key from anothertable)
-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED]
Sent: Monday, January 28, 2008 7:54 AM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Number of elements in IN clause
>SELECT * FROM maintable WHERE key IN stuff;
Thanks for that tip. Didn't know you could do that.
Can't remember this as standard SQL.
RBS
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 28 January 2008 12:40
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Number of elements in IN clause
Felix Radensky <[EMAIL PROTECTED]> wrote:
> Hi,
>
> Is there any limitation on the number of elements in IN clause ?
> Can one have, e.g. thousands of elements ? Also, can having to many
> elements become inefficient at some point and one has to use some
> other technique, i.e. comparing elements one by one in a loop ?
>
You can create a table that contains the elements that you would
normally put in your IN clause:
CREATE TEMP TABLE stuff(x);
INSERT INTO stuff VALUES('one');
INSERT INTO stuff VALUES('two');
INSERT INTO stuff VALUES('one million');
Then run your query this way:
SELECT * FROM maintable WHERE key IN stuff;
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Number of elements in IN clause
I don't think it is standard SQL. At the very least, it doesn't work in MSSQL. Standard is SELECT * FROM maintable WHERE key IN (select x from stuff); SQLite shortened version is much nicer.. wish it was standard. Sam --- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Monday, January 28, 2008 7:54 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Number of elements in IN clause >SELECT * FROM maintable WHERE key IN stuff; Thanks for that tip. Didn't know you could do that. Can't remember this as standard SQL. RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Number of elements in IN clause
>SELECT * FROM maintable WHERE key IN stuff;
Thanks for that tip. Didn't know you could do that.
Can't remember this as standard SQL.
RBS
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 28 January 2008 12:40
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Number of elements in IN clause
Felix Radensky <[EMAIL PROTECTED]> wrote:
> Hi,
>
> Is there any limitation on the number of elements in IN clause ?
> Can one have, e.g. thousands of elements ? Also, can having
> to many elements become inefficient at some point and one
> has to use some other technique, i.e. comparing elements one
> by one in a loop ?
>
You can create a table that contains the elements that you would
normally put in your IN clause:
CREATE TEMP TABLE stuff(x);
INSERT INTO stuff VALUES('one');
INSERT INTO stuff VALUES('two');
INSERT INTO stuff VALUES('one million');
Then run your query this way:
SELECT * FROM maintable WHERE key IN stuff;
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Number of elements in IN clause
Felix Radensky <[EMAIL PROTECTED]> wrote:
> Hi,
>
> Is there any limitation on the number of elements in IN clause ?
> Can one have, e.g. thousands of elements ? Also, can having
> to many elements become inefficient at some point and one
> has to use some other technique, i.e. comparing elements one
> by one in a loop ?
>
You can create a table that contains the elements that you would
normally put in your IN clause:
CREATE TEMP TABLE stuff(x);
INSERT INTO stuff VALUES('one');
INSERT INTO stuff VALUES('two');
INSERT INTO stuff VALUES('one million');
Then run your query this way:
SELECT * FROM maintable WHERE key IN stuff;
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
