Re: [sqlite] WHERE col IN tab
On Fri, Sep 16, 2016 at 6:50 PM, Richard Hipp wrote: > On 9/16/16, Dominique Devienne wrote: > > Is that <> SQL standard? > > That feature was added to SQLite on 2004-01-15 > (http://sqlite.org/src/timeline?c=01874d25). I do not recall why I > added it. > On Mon, Sep 19, 2016 at 4:56 PM, James K. Lowden wrote: > On Fri, 16 Sep 2016 16:59:17 +0200 > Dominique Devienne wrote: > > > Is that <> SQL standard? > No. > Thank you Richard and James. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE col IN tab
On Fri, 16 Sep 2016 16:59:17 +0200 Dominique Devienne wrote: > Is that <> SQL standard? No. The two most frequently used pointless words in SQL are "select *". The SELECT clause (not statement) chooses columns; in relational algebra terms, it's a project operator. If "all columns" is what you need, what you don't need is projection. Requiring "select *" makes as much sense as requiring "WHERE TRUE" if there is no restriction. The strict select-from-where construct in SQL is an artifact of its roots in IBM's 1970s-era "4th generation" languages. That's why the language looks so much like Cobol and so little like math. But it is what it is. In SQL, a tablename is a parameter for FROM (and nowadays, JOIN). Predicates -- IN, EXISTS -- take SELECT. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE col IN tab
On 9/16/16, Dominique Devienne wrote: > > Is that <> SQL standard? That feature was added to SQLite on 2004-01-15 (http://sqlite.org/src/timeline?c=01874d25). I do not recall why I added it. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE col IN tab
On Fri, Sep 16, 2016 at 6:00 PM, J Decker wrote: > but probably what you mean is... > I didn't mean anything. I asked a question about an unusual syntax. > SELECT * FROM t1 join T2 on x=y; > SELECT * FROM t1 join (select y from t2) on x=y > A join works too, but that's beside the point. Logically I don't see any difference with <> and a join when not accessing columns from the "joined" on column in the select list. A good query planner will use as good a plan in both situation. but I'm not a SQL expert. select * from table where colName in (1,2,3,4) /// woud return rows where > some column has a value of 1,2,3 or 4 > And? if those values are rows in a single-column table, as I already demonstrated in the original post, that's exactly the same as a literal list (or a join) logically. The question was about that <> syntax SQLite allows. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE col IN tab
IN is for sets, not another table. I'm surprised sqlite didn't thrown an error but probably what you mean is... SELECT * FROM t1 join T2 on x=y; SELECT * FROM t1 join (select y from t2) on x=y select * from table where colName in (1,2,3,4) /// woud return rows where some column has a value of 1,2,3 or 4 On Fri, Sep 16, 2016 at 7:59 AM, Dominique Devienne wrote: > Reading https://www.sqlite.org/src/tktview/0eab1ac7591f, > (from a very recent thread) I was surprised to read that syntax. > > So I tried it in SQLite, and it works as shown in the ticket: > > C:\Users\DDevienne>sqlite3 > SQLite version 3.10.2 2016-01-20 15:27:19 > sqlite> CREATE TABLE t1(x INTEGER PRIMARY KEY NOT NULL, a,b,c,d); > sqlite> CREATE TABLE t2(y INT); > sqlite> insert into t1 values (1, 1,1,1,1), (2, 2,2,2,2); > sqlite> insert into t2 values (2); > sqlite> SELECT * FROM t1 WHERE x IN t2; > 2|2|2|2|2 > sqlite> SELECT * FROM t1 WHERE x IN (select y from t2); > 2|2|2|2|2 > > But when I try the same thing with Oracle in SQL Dev OTOH, it fails: > > create table t (x number primary key, a number) > create table tt (y number) > SELECT * FROM t WHERE x IN tt > ORA-00904: "TT": invalid identifier > SELECT * FROM t WHERE x IN (select y from tt) > OK (no rows) > > Is that <> SQL standard? > Or an SQLite specific extension? Available in other RDBMs? > > Just curious. Thanks, --DD > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
