When you are executing the query: SELECT * FROM item_info JOIN (select count(1) from users group by item_id) USING (item_id) where item_id = ?;
You are telling the SQL Database Engine (whatever it may happen to be, in this case SQLite3) that you want to take the table produced by: select * from item_info; and join it (an equijoin) against the table produced by running the query: select count(1) from users group by item_id; based on the equality of the column item_id in both tables. If you run the two queries you will see that the second table DOES NOT produce a column called item_id. Therefore you cannot join those tables and instead you get an error message telling you that the column item_id does not exist in both tables. FROM tables are siblings not correlates. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Jonathan Moules >Sent: Wednesday, 2 January, 2019 15:21 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] Optimisation opportunity on subquery? > >Hi Simon, > >Thanks for that - I'm always trying to improve my SQL. I think I see >what you mean now. > >Assuming my original query: > > SELECT > * > FROM > item_info > JOIN (select count(1) from users group by item_id) > USING (item_id) > where item_id = ?; > >There are three uses of "item_id". > >* The first is the "group by", which unless my SQL is even worse than >I >imagine, can only be referencing the users table. > >* The second is "USING" - which is referencing both. > >* The third is the one I guess you mean is ambiguous? My thinking was >that because item_id is going through the USING it wasn't ambiguous >as >they're the same thing; though that's apparently not how the query >planner sees it, and hence your reference to ambiguity. That right? > >So I tried using aliases (I'm assuming that removes the ambiguity), >but >the query times remained at about 0.5s for both versions (whether >i.item_id or u.item_id): > > SELECT > * > FROM > item_info i > JOIN (select count(1) from users group by item_id) u > USING (item_id) > where u.item_id = ?; > >Thanks again for clarifying, but after checking, it doesn't seem like >it >was an ambiguity thing. >Cheers, >Jonathan > >On 2019-01-02 22:04, Simon Slavin wrote: >> On 2 Jan 2019, at 9:50pm, Jonathan Moules <jonathan- >li...@lightpear.com> wrote: >> >>> Sorry, but which column is ambiguous? The users.item_id is a >foreign key to the item_info.item_id - that's why it's a "REFERENCES" >- why would I want to change it to be something else? Isn't the >convention for FK's to have the same name across tables? That's what >"USING" is for right? (or NATURAL, but I prefer to be explicit.) >Happy to be corrected. >> It may be that our careers developed with different ideas about how >to use SQL. You had a JOIN, both tables had a column "item_id", and >a reference inside the JOIN to "item_id" would be ambiguous. Since >the SQL standard does not make it clear which table would be used, it >would be possible for different implementations of SQL to think you >meant different tables. >> >> I understand that, in your example, the values would be the same. >But that doesn't explain to you what the optimizer thinks you're >trying to do. The simplest way to tell the optimizer what you need >would be to rename one of the columns. You could try both tables, >see which solution was faster, and use that one. >> >> However, I see other posters have gained better clarity for your >problem. >> >> Simon. >> _______________________________________________ >> 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users