Embarrassing confession time: I didn't think you could use "using" to do this while selecting "a.*"
https://www.sqlite.org/lang_select.html "For each pair of columns identified by a USING clause, the column from the right-hand dataset is omitted from the joined dataset. This is the only difference between a USING clause and its equivalent ON constraint." I thought it literally took that column out of the result set. So "a SOME SORT OF JOIN b USING (foo)" literally got rid of a.foo and b.foo and put the value of a.foo into a "foo" column, or literally got rid of b.foo. And that trying to select a.foo, or especially b.foo would raise an error of it not being an existing column. I didn't realize that the column omission <only> happens with you "select * from" and not for anything else. The more you know. SQLite version 3.24.0 2018-06-04 19:24:41 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table a (foo, bar); sqlite> create table b (foo, baz); sqlite> insert into a values (1, 2), (2, 3), (3, 4); sqlite> insert into b values (1, 7), (4, 5); sqlite> select * from a inner join b using (foo);--knew this would only have 3 result columns foo|bar|baz 1|2|7 sqlite> select a.*, b.* from a inner join b using (foo);--kinda thought this would be an error, or omit foo foo|bar|foo|baz 1|2|1|7 sqlite> select b.foo from a inner join b using (foo);--definitely thought this was an error foo 1 sqlite> delete from b; sqlite> insert into b values (1.0, 7), (4, 5); sqlite> select * from a inner join b using (foo); foo|bar|baz 1|2|7 sqlite> select a.*, b.* from a inner join b using (foo); foo|bar|foo|baz 1|2|1.0|7 sqlite> select foo, a.foo, b.foo, a.bar, b.baz from a inner join b using (foo); foo|foo|foo|bar|baz 1|1|1.0|2|7 sqlite> -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Jay Kreibich Sent: Tuesday, August 28, 2018 1:18 PM To: SQLite mailing list Subject: Re: [sqlite] How to find records in a table which are not in a second table? > On Aug 28, 2018, at 11:30 AM, Joe <spec...@joepgen.com> wrote: > > A (perhaps silly ) beginners question: > My sqlite database contains several tables, two of them, table A and table B, > have text colums called 'nam'. The tables have about 2 millions lines. > What's the most efficient way to select all lines from table A with nam > values, which are not present in the nam values of table B? > Thanks — Joe Not sure about performance vs other suggestions, but this is a common way of doing that: SELECT a.* FROM a LEFT JOIN b USING (nam) WHERE b.nam IS NULL -j _______________________________________________ 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
