Since LEFT JOIN and RIGHT JOIN while also swapping the tables are interchangeable, why should this not work?
SELECT ... FROM clients LEFT JOIN ON ... ( suppliers LEFT JOIN stock ON ...) WHERE ...; -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von R Smith Gesendet: Mittwoch, 22. März 2017 14:30 An: sqlite-users@mailinglists.sqlite.org Betreff: Re: [sqlite] RIGHT JOIN! still not supported? On 2017/03/22 9:53 AM, Eric Grange wrote: > For the sake of curiosity, is anyone (as in any human) using RIGHT JOIN? > > Personally I never had a need for a RIGHT JOIN, not because of > theoretical or design considerations, but it just never came into my > flow of thought when writing SQL... > > I guess some automated SQL query generators could use it though, > because they do not have a "flow of thought". I had a need of it the other day (something that can easily be overcome with a CTE or subquery, but still may have made the sql read more natural): We have one system which contains (among other things) clients, suppliers and stock tables. A stock item will mostly have a supplier and mostly will have an intended client (the production is bespoke) except for a few common materials. So we needed a list of all suppliers linked to all clients affected by them (via Stock), and also see the clients who are not affected by any suppliers (which should really be a minimum number) and the suppliers who are not affecting any clients (which should also be a small list). Note that not all suppliers may be linked to live stock items, and not all clients may be linked to live stock items either - but they all need to show up in the list. If I can try my hand at a schematic of the sets showing links: clients: [ A B C D E ] - links | | | stock: [ 1 2 3 4 5 ] - links | | | suppliers: [ @ # $ % & ! ] I would need to see a "linking" query result showing overlap and non-links like this: . clnt | supp| stck . ---- | ---- | ---- . A | NULL | NULL . B | NULL| 1 . C | NULL| NULL . D | $ | 2 . E | & | 4 . NULL | @ | NULL . NULL | # | NULL . NULL | % | 3 . NULL | ! | NULL Note that I don't actually need the stck column, just adding it to show more sensible results. Also note that items linked to neither client nor supplier need not show up (stock item 5 in this case), so I only need results having either a client or a supplier. The obvious solution was: SELECT clients.name, suppliers.name FROM clients LEFT JOIN stock ON stock.client = clients.id RIGHT JOIN suppliers ON stock.supplier = suppliers.id WHERE (clients.name IS NOT NULL) OR (suppliers.name IS NOT NULL) ; I suppose this could have worked too: SELECT clients.name, suppliers.name FROM suppliers LEFT JOIN stock ON stock.supplier = suppliers.id RIGHT JOIN clients ON stock.client = clients.id WHERE (clients.name IS NOT NULL) OR (suppliers.name IS NOT NULL) ; or even this: SELECT clients.name, suppliers.name FROM stock RIGHT JOIN suppliers ON stock.supplier = suppliers.id RIGHT JOIN clients ON stock.client = clients.id WHERE (clients.name IS NOT NULL) OR (suppliers.name IS NOT NULL) ; I don't see how to achieve that with simple joins in sqlite3. We used some CTEs to LEFT JOIN stock to suppliers, and then left-joined that CTE on the stock to the clients - which would also work using sub-query joins or filtered cross-joins (much slower). So to add to the discussion: Yeah, we've needed it, but overcoming the problem was not exactly difficult and the use case itself is certainly not very common (or even mildly common, it's rather uncommon). Cheers, Ryan _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
