[Dbix-class] selecting records with no links-to

[Len Jaffe]

On Fri, Jun 14, 2013 at 9:10 AM, Ronald Kimball <rkimball at snapapp.com>wrote:

> On Fri, Jun 14, 2013 at 7:31 AM, Dave Howorth <dhoworth at mrc-lmb.cam.ac.uk=
>wrote:
>
>>
>>
>> I want a resultset containing all artists which have NO associated CD.
>>
>> What's the appropriate way to select this resultset?
>>
>
> I think you'd want to do a left outer join where cd's primary key is null.
>

The outer join is almost always faster then the IN/NOT IN operator since
you've almost always got the foreign key relationship indexed, whereas you
don't have an index into the set on which you're perfooming the IN/NOT IN
test.

Also, for the benefit of those readers who may not be familiar with the
outer join, it is a join type that returns a tuple even if the join
condition goes unsatisfied. A LEFT OUTER JOIN returns a tuple containing
the dat aform the left side of the join condition, and NULLs for all of the
selected values form the right side A RIGHT OUTER JOIN returns the opposite.

As a result, to find the rows from table 1 where no related record exists
in table 2, one add the 'WHERE table2.col1 IS NULL'  making sure that col1
is the column from the foreign key relation (which will be null if the row
does not exist).

L.





-- =

lenjaffe at jaffesystems.com   614-404-4214    @lenjaffe
Software Enginer, Founder Volunteerable <http://www.volunteerable..com/>,
Host of Columbus Code Jam <http://www.meetup.com/techlifecolumbus/>
www.lenjaffe.com     http://www.theycomewithcheese.com
Perl Advent Planet <http://www.lenjaffe.com/PerlAdventPlanet/> - Advent
Calendars: Perlish and otherwise.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20130614/e07=
144bf/attachment.htm