Re: [sqlite] RIGHT JOIN! still not supported?
On Wed, 22 Mar 2017 10:53:09 -0500 Jeffrey Mattoxwrote: > Isn't it possible to get the same results of a RIGHT JOIN by using > two selects with a UNION or UNION ALL between them. Yes. By definition, an outer join is the union of the rows that meet the matching criteria and the rows that do not meet the matching criteria The outer join construct exists for convenience: the convenience of the user, who expresses the idea with fewer words, and the convenience of the implementer, whose query optimization problem is simplified by the direct expression of the desired result. An outer join says explicitly it wants all rows in the outer table. On the other hand, the query planner has to be quite sophisticated to recognize that A = (A where exists B) union (A where not exists B) --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] RIGHT JOIN! still not supported?
After some thinking I came up with this: First, set up the example (note: no CTE as I am still runnning SQLIte 3.7.14.1): CREATE temp TABLE stock(id, cid, sid); CREATE temp TABLE clients(id,name); CREATE temp TABLE suppliers(id,name); insert into stock (id) values (1),(2),(3),(4),(5),(6),(7); UPDATE stock SET cid = id WHERE id < 4; UPDATE stock SET sid = id - 1 WHERE id BETWEEN 2 AND 5; INSERT INTO clients SELECT cid, 'Hick'||cid FROM stock WHERE cid IS NOT NULL; INSERT INTO suppliers SELECT sid, 'Smith'||sid FROM stock WHERE sid IS NOT NULL; The desired output contains rows that have a NULL client, so may as well create a view for this (NOTE: renaming the columns to indicate the table they are from): create temp view clients_null as select id as cid,name as cname from clients union all select null, null; The same holds for rows that have a NULL supplier, so create a view for this too (NOTE: renaming as above): create temp view suppliers_null as select id as sid,name as sname from suppliers union all select null, null; Now we are ready to produce all possible rows, may as well do this with a view too: create temp view all_rows as select * from clients_null, suppliers_null; And for the grand finale, join with stock to retrieve 1) stock connecting client to supplier 2) client with stock not connected to supplier 3) supplier with stock not connected to client (NOTE: not a single LEFT or RIGHT join required) (NOTE: if we could indicate that NULL == NULL should be true, just the first expressions should suffice): select cname,sname,id from all_rows a join stock s on ((a.sid = s.sid) and (a.cid = s.cid)) or ((a.cid = s.cid) and (a.sid is null) and (s.sid is NULL)) or ((a.sid = s.sid) and (a.cid is null) and (s.cid is NULL)); cname|sname|id Hick1|NULL|1 Hick2|Smith1|2 Hick3|Smith2|3 NULL|Smith3|4 NULL|Smith4|5 Adding a dummy value instead of NULL yields (NOTE: where clause to eliminate totally unconnected stock items): select cname,sname,id from all_rows a join stock s on (ifnull(a.sid,-1) = ifnull(s.sid,-1)) and (ifnull(a.cid,-1) = ifnull(s.cid,-1)) where cname not null or sname not null; Pursuing this idea further leads to: drop view clients_null; drop view suppliers_null; create temp view clients_null as select id as cid,name as cname from clients union all select -1, null; create temp view suppliers_null as select id as sid,name as sname from suppliers union all select -1, null; create temp view stock_dummy as select id,ifnull(sid,-1) as sid,ifnull(cid,-1) as cid from stock; and the quite readable statement: select cname,sname,id from all_rows a join stock_dummy s on (a.sid = s.sid) and (a.cid = s.cid) where cname not null or sname not null; ___ 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
Re: [sqlite] RIGHT JOIN! still not supported?
Just to be sure, have you actually checked your right join syntax on a system that supports it? Because I don't think what you have written there will actually achieve what you think it will. There is no three-way join operator that will perform a left and a right join . No matter how you word it, one has to happen first and the other second. Using the small sample you gave in one of the emails... clients left join stock right join suppliers (clients left join stock...) right join suppliers (all clients but only stock related to a client) right join suppliers (Hick1|1 Hick2|2 Hick3|3) right join suppliers Hick1|1|Smith1 Hick2|2|Smith2 Hick3|3|Smith3 NULL|NULL|Smith4 You need a union in there to actually accomplish what you're looking for. I put the small schema into Access, which does allow for right joins, and when putting in your query I get: "The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement." So yeah, right join will not allow you to perform 2 joins at the same time. You need a union in there. Since every join type has the same precedence this isn't the case of "2 + 2 / 2" "A join B join C" will always be "(A join B) join C" "A LeftJoin B RightJoin C" will always be "(A LeftJoin B) RightJoin C"... which is the same as "C LeftJoin (A LeftJoin B)" So you can always re-write a right join as a left join, you're not losing any power. (It may be annoying to rewrite yes, but not any less functional) -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Wednesday, March 22, 2017 9:30 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] RIGHT JOIN! still not supported? ... 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] RIGHT JOIN! still not supported?
Unless I misunderstand the desired result, this query would be better formulated using 2 left joins instead, like this: SELECT ... FROM Persons LEFT JOIN Pets ... LEFT JOIN PetAccessories ... -- Darren Duncan On 2017-03-22 2:22 AM, Chris Locke wrote: An interesting discussion of it on StackOverflow... http://stackoverflow.com/questions/689963/does-anyone-use-right-outer-joins To give one example where a RIGHT JOIN may be useful. Suppose that there are three tables for People, Pets, and Pet Accessories. People may optionally have pets and these pets may optionally have accessories. If the requirement is to get a result listing all people irrespective of whether or not they own a pet and information about any pets they own that also have accessories. All in all probably easiest to use a RIGHT JOIN SELECT P.PersonName, Pt.PetName, Pa.AccessoryName FROM Pets Pt JOIN PetAccessories Pa ON Pt.PetName = Pa.PetName RIGHT JOIN Persons P ON P.PersonName = Pt.PersonName; Though if determined to avoid this another option would be to introduce a derived table that can be left joined to. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] RIGHT JOIN! still not supported?
On 2017/03/22 4:14 PM, Hick Gunter wrote: 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 ...; HI Gunter, They are interchangeable for 1 vs. 1 join queries, but as soon as you add a second lookup, they are not. Your query won't work because this will not show ANY items that are NOT explicitly part of the clients table. This is the problem with left-joins. Your query will produce output like this (from my example below): . | | . A | NULL | NULL . B | NULL | 1 . C | NULL | NULL . D | $ | 2 . E | & | 4 You can see how that is different from what we needed in the original post. To save time, I can distill the problem for you to the basics in a script: -- -- SET-UP: CREATE TABLE stock(id, cid, sid); CREATE TABLE clients(id,name); CREATE TABLE suppliers(id,name); WITH CC(id) AS (SELECT 1 UNION ALL SELECT id+1 FROM CC WHERE id<7) INSERT INTO stock(id) SELECT id FROM CC; UPDATE stock SET cid = id WHERE id < 4; UPDATE stock SET sid = id - 1 WHERE id BETWEEN 2 AND 5; INSERT INTO clients SELECT cid, 'Hick'||cid FROM stock WHERE cid IS NOT NULL; INSERT INTO suppliers SELECT sid, 'Smith'||sid FROM stock WHERE sid IS NOT NULL; -- Show content: SELECT * FROM stock; -- id | cid| sid -- | -- | -- -- 1 | 1 | NULL -- 2 | 2 | 1 -- 3 | 3 | 2 -- 4 | NULL | 3 -- 5 | NULL | 4 -- 6 | NULL | NULL -- 7 | NULL | NULL SELECT * FROM clients; -- id | name -- | --- -- 1 | Hick1 -- 2 | Hick2 -- 3 | Hick3 SELECT * FROM suppliers; -- id | name -- | -- 1 | Smith1 -- 2 | Smith2 -- 3 | Smith3 -- 4 | Smith4 -- One cumbersome solution to get the correct output: SELECT clients.name, suppliers.name, stock.id FROM clients LEFT JOIN stock ON stock.cid=clients.id LEFT JOIN suppliers ON stock.sid = suppliers.id UNION SELECT clients.name, suppliers.name, stock.id FROM suppliers LEFT JOIN stock ON stock.sid=suppliers.id LEFT JOIN clients ON stock.cid = clients.id ORDER BY 3 ; -- name| name | id -- --- | | -- Hick1 | NULL | 1 -- Hick2 | Smith1 | 2 -- Hick3 | Smith2 | 3 -- NULL| Smith3 | 4 -- NULL| Smith4 | 5 -- Gunter's suggested query (I think, unless I got it wrong): SELECT clients.name, A.name, stock.id FROM clients LEFT JOIN stock ON stock.cid = clients.id LEFT JOIN (SELECT stock.id, name FROM suppliers LEFT JOIN stock ON stock.sid = suppliers.id) AS A ON A.id = stock.id ORDER BY 3 ; -- name| name | id -- --- | | -- Hick1 | NULL | 1 -- Hick2 | Smith1 | 2 -- Hick3 | Smith2 | 3 -- Cleanup; DROP TABLE suppliers; DROP TABLE clients; DROP TABLE stock; As you can see, they are not the same - Smith3 & 4 never shows up in the second query. If you can say how to get to the correct query using simple joins (without the Union which is slow on large tables because of duplication, and without CTEs), I'd be both impressed and thankful. -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 li
Re: [sqlite] RIGHT JOIN! still not supported?
On Wed, Mar 22, 2017 at 9:30 AM, R Smithwrote: > > 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): > I've also needed it for reasons I can't remember, but I know I've used the functionality. RIGHT JOIN is a pretty limited use case, but handy when needed. I think this OP came from another DBMS software, such as MS, that does support RIGHT JOIN. (At least from what memory serves when I took my MS courses back when MSSQL2K was just coming out) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] RIGHT JOIN! still not supported?
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
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
Re: [sqlite] RIGHT JOIN! still not supported?
You don't need derived tables, just use brackets for explicitly order the execution of JOIN operators like this: SELECT P.PersonName , Pt.PetName , Pa.AccessoryName FROMPersons P LEFT JOIN ( Pets Pt JOINPetAccessories Pa ON Pt.PetName = Pa.PetName) ON P.PersonName = Pt.PersonName; JOIN ordering is handy especially if you have to LEFT JOIN more tables to Persons, then using RIGHT JOIN obfuscates the code unnecessary. IMO this ordering pattern comes up often in reporting queries. And this reminds me of "How much is 2 + 2 * 2?" puzzle on calculator vs excel :-)) cheers, -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Chris Locke Sent: Wednesday, March 22, 2017 11:22 AM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] RIGHT JOIN! still not supported? An interesting discussion of it on StackOverflow... http://stackoverflow.com/questions/689963/does-anyone-use-right-outer-joins To give one example where a RIGHT JOIN may be useful. Suppose that there are three tables for People, Pets, and Pet Accessories. People may optionally have pets and these pets may optionally have accessories. If the requirement is to get a result listing all people irrespective of whether or not they own a pet and information about any pets they own that also have accessories. All in all probably easiest to use a RIGHT JOIN SELECT P.PersonName, Pt.PetName, Pa.AccessoryName FROM Pets Pt JOIN PetAccessories Pa ON Pt.PetName = Pa.PetName RIGHT JOIN Persons P ON P.PersonName = Pt.PersonName; Though if determined to avoid this another option would be to introduce a derived table that can be left joined to. On Wed, Mar 22, 2017 at 7:53 AM, Eric Grange <egra...@glscene.org> 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". > > On Tue, Mar 21, 2017 at 9:50 PM, Darren Duncan > <dar...@darrenduncan.net> > wrote: > > > What benefit does a RIGHT JOIN give over a LEFT JOIN? What queries > > are more natural to write using the first rather than the second? > > > > While I can understand arguments based on simple mirror parity, eg > > we > have > > < so we should have > too, lots of other operations don't have > > mirror syntax either. > > > > -- Darren Duncan > > > > On 2017-03-21 8:42 AM, Daniel Kamil Kozar wrote: > > > >> Seeing how SQLite was created in 2000, it seems like nobody really > >> needed this feature for the last 17 years enough in order to > >> actually implement it. > >> > >> Last I heard, patches are welcome on this mailing list. Don't keep > >> us waiting. > >> > >> Kind regards, > >> Daniel > >> > >> On 20 March 2017 at 21:09, PICCORO McKAY Lenz > >> <mckaygerh...@gmail.com> > >> wrote: > >> > >>> i got this > >>> > >>> Query Error: RIGHT and FULL OUTER JOINs are not currently > >>> supported Unable to execute statement > >>> > >>> still today in 21 ts century? > >>> > >>> Lenz McKAY Gerardo (PICCORO) > >>> http://qgqlochekone.blogspot.com > >>> > >> > > ___ > > 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] RIGHT JOIN! still not supported?
An interesting discussion of it on StackOverflow... http://stackoverflow.com/questions/689963/does-anyone-use-right-outer-joins To give one example where a RIGHT JOIN may be useful. Suppose that there are three tables for People, Pets, and Pet Accessories. People may optionally have pets and these pets may optionally have accessories. If the requirement is to get a result listing all people irrespective of whether or not they own a pet and information about any pets they own that also have accessories. All in all probably easiest to use a RIGHT JOIN SELECT P.PersonName, Pt.PetName, Pa.AccessoryName FROM Pets Pt JOIN PetAccessories Pa ON Pt.PetName = Pa.PetName RIGHT JOIN Persons P ON P.PersonName = Pt.PersonName; Though if determined to avoid this another option would be to introduce a derived table that can be left joined to. On Wed, Mar 22, 2017 at 7:53 AM, Eric Grangewrote: > 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". > > On Tue, Mar 21, 2017 at 9:50 PM, Darren Duncan > wrote: > > > What benefit does a RIGHT JOIN give over a LEFT JOIN? What queries are > > more natural to write using the first rather than the second? > > > > While I can understand arguments based on simple mirror parity, eg we > have > > < so we should have > too, lots of other operations don't have mirror > > syntax either. > > > > -- Darren Duncan > > > > On 2017-03-21 8:42 AM, Daniel Kamil Kozar wrote: > > > >> Seeing how SQLite was created in 2000, it seems like nobody really > >> needed this feature for the last 17 years enough in order to actually > >> implement it. > >> > >> Last I heard, patches are welcome on this mailing list. Don't keep us > >> waiting. > >> > >> Kind regards, > >> Daniel > >> > >> On 20 March 2017 at 21:09, PICCORO McKAY Lenz > >> wrote: > >> > >>> i got this > >>> > >>> Query Error: RIGHT and FULL OUTER JOINs are not currently supported > >>> Unable to execute statement > >>> > >>> still today in 21 ts century? > >>> > >>> Lenz McKAY Gerardo (PICCORO) > >>> http://qgqlochekone.blogspot.com > >>> > >> > > ___ > > 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
Re: [sqlite] RIGHT JOIN! still not supported?
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". On Tue, Mar 21, 2017 at 9:50 PM, Darren Duncanwrote: > What benefit does a RIGHT JOIN give over a LEFT JOIN? What queries are > more natural to write using the first rather than the second? > > While I can understand arguments based on simple mirror parity, eg we have > < so we should have > too, lots of other operations don't have mirror > syntax either. > > -- Darren Duncan > > On 2017-03-21 8:42 AM, Daniel Kamil Kozar wrote: > >> Seeing how SQLite was created in 2000, it seems like nobody really >> needed this feature for the last 17 years enough in order to actually >> implement it. >> >> Last I heard, patches are welcome on this mailing list. Don't keep us >> waiting. >> >> Kind regards, >> Daniel >> >> On 20 March 2017 at 21:09, PICCORO McKAY Lenz >> wrote: >> >>> i got this >>> >>> Query Error: RIGHT and FULL OUTER JOINs are not currently supported >>> Unable to execute statement >>> >>> still today in 21 ts century? >>> >>> Lenz McKAY Gerardo (PICCORO) >>> http://qgqlochekone.blogspot.com >>> >> > ___ > 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
Re: [sqlite] RIGHT JOIN! still not supported?
What benefit does a RIGHT JOIN give over a LEFT JOIN? What queries are more natural to write using the first rather than the second? While I can understand arguments based on simple mirror parity, eg we have < so we should have > too, lots of other operations don't have mirror syntax either. -- Darren Duncan On 2017-03-21 8:42 AM, Daniel Kamil Kozar wrote: Seeing how SQLite was created in 2000, it seems like nobody really needed this feature for the last 17 years enough in order to actually implement it. Last I heard, patches are welcome on this mailing list. Don't keep us waiting. Kind regards, Daniel On 20 March 2017 at 21:09, PICCORO McKAY Lenzwrote: i got this Query Error: RIGHT and FULL OUTER JOINs are not currently supported Unable to execute statement still today in 21 ts century? Lenz McKAY Gerardo (PICCORO) http://qgqlochekone.blogspot.com ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] RIGHT JOIN! still not supported?
Seeing how SQLite was created in 2000, it seems like nobody really needed this feature for the last 17 years enough in order to actually implement it. Last I heard, patches are welcome on this mailing list. Don't keep us waiting. Kind regards, Daniel On 20 March 2017 at 21:09, PICCORO McKAY Lenzwrote: > i got this > > Query Error: RIGHT and FULL OUTER JOINs are not currently supported > Unable to execute statement > > still today in 21 ts century? > > Lenz McKAY Gerardo (PICCORO) > http://qgqlochekone.blogspot.com > ___ > 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
Re: [sqlite] RIGHT JOIN! still not supported?
Sqlite is public domain, so feel free to add the necessary code, and once approved, it'll get added to the main code. Thanks, Chris On 20 Mar 2017 8:09 p.m., "PICCORO McKAY Lenz"wrote: > i got this > > Query Error: RIGHT and FULL OUTER JOINs are not currently supported > Unable to execute statement > > still today in 21 ts century? > > Lenz McKAY Gerardo (PICCORO) > http://qgqlochekone.blogspot.com > ___ > 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] RIGHT JOIN! still not supported?
i got this Query Error: RIGHT and FULL OUTER JOINs are not currently supported Unable to execute statement still today in 21 ts century? Lenz McKAY Gerardo (PICCORO) http://qgqlochekone.blogspot.com ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users