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:
[hidden email]] 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 <
[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Locked
