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
FROM Persons P
LEFT JOIN ( Pets Pt
JOIN PetAccessories 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,
</wqw>
-----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
