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 <ddevie...@gmail.com> 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 <<WHERE col IN tab>> 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
