Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

On 9 Nov 2014, at 1:49pm, Tristan Van Berkom wrote: > This year in particular I've been faced with my first queries of modest > complexity, see for example this (temporary) paste: > >http://www.fpaste.org/148918/41545194/ > > This is the beginnings of a query

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

On Sun, 2014-11-09 at 15:04 +0200, RSmith wrote: > On 2014/11/09 14:11, Tristan Van Berkom wrote: > >> A good SQL rule of thumb: if you can think of a way, so can the DBMS. "... > >> no opportunity to make a good guess" is not true. In > >> some sense, SQLite has had 10 years to make a good

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

On 2014/11/09 14:11, Tristan Van Berkom wrote: A good SQL rule of thumb: if you can think of a way, so can the DBMS. "... no opportunity to make a good guess" is not true. In some sense, SQLite has had 10 years to make a good guess, and often does. A nested select need not be materialized as a

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

On Sat, 2014-11-08 at 14:27 -0500, James K. Lowden wrote: > On Sun, 09 Nov 2014 00:45:16 +0900 > Tristan Van Berkom wrote: > > > While I do understand SQL as a functional language, most functional > > programming I've done still has rather explicit syntax/rules, so I

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

On Sun, 09 Nov 2014 00:45:16 +0900 Tristan Van Berkom wrote: > While I do understand SQL as a functional language, most functional > programming I've done still has rather explicit syntax/rules, so I get > particularly uncomfortable with writing vague statements, such

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

On Sat, 2014-11-08 at 09:46 -0700, Keith Medcalf wrote: > On Saturday, 8 November, 2014 06:56, Tristan Van Berkom > said: > > >On Sat, 2014-11-08 at 06:23 -0700, Keith Medcalf wrote: > >> How about the direct approach: > >> > >> SELECT uid > >> FROM resource > >>

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

There's nothing vague about select statements, they're logical formulas involving the data in your database and as exact as any other programming language, albeit in a very different domain. Relational databases are based on first order predicate logic and have operations that are are rigorously

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

On Saturday, 8 November, 2014 06:56, Tristan Van Berkom said: >On Sat, 2014-11-08 at 06:23 -0700, Keith Medcalf wrote: >> How about the direct approach: >> >> SELECT uid >> FROM resource >> WHERE uid NOT IN (SELECT resource_uid >> FROM

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

On Sat, 2014-11-08 at 10:23 -0500, James K. Lowden wrote: > On Sat, 08 Nov 2014 22:55:46 +0900 > Tristan Van Berkom wrote: > > > So I would have to say, the "right way to do it" is the most efficient > > way, the one which provides SQLite with the best indications of

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

On Sat, 08 Nov 2014 22:55:46 +0900 Tristan Van Berkom wrote: > So I would have to say, the "right way to do it" is the most efficient > way, the one which provides SQLite with the best indications of how > to plot an efficient query plan. Keith is suggesting that the

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

On Sat, 2014-11-08 at 06:23 -0700, Keith Medcalf wrote: > How about the direct approach: > > SELECT uid > FROM resource > WHERE uid NOT IN (SELECT resource_uid > FROM event_participant, event > WHERE event_participant.event_uid = event.uid >

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

How about the direct approach: SELECT uid FROM resource WHERE uid NOT IN (SELECT resource_uid FROM event_participant, event WHERE event_participant.event_uid = event.uid AND event.shift_uid = :shift_uid AND

[sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

Hi all, Today I've stumbled on a situation where I think I really need to use a RIGHT OUTER JOIN, and looking at all the examples on the internet I could find so far, I'm not finding a way to simulate it properly using LEFT OUTER JOINs. So I thought, before I commit to an inefficient alternative