Re: anti-join with SQL::Statement?

On Mon, Oct 04, 2010 at 11:12:05AM -0700, Darren Duncan wrote:
> Reiner, I don't know if this was your workaround, but here's another
> solution that just uses SQL::Statement ...
> 
> Ludwig, Michael wrote:
> >>So the next thing I tried was the anti-join (see
> >>
> >>  http://en.wikipedia.org/wiki/Relational_algebra#Antijoin
> >
> >First time I hear of this "antijoin".
> >
> >The example given on the Wiki page doesn't make much sense
> >to me. Isn't is simply an OUTER JOIN where you're looking
> >for NULL on the right-hand side?
> >
> >SELECT E.Name, D.DeptName
> >FROM Employee AS E
> >LEFT JOIN Dept AS D ON E.DeptName = D.DeptName
> >WHERE D.DeptName IS NULL

I tried this, but it didn't work: none of the D.DeptNames were NULL.
However, on retrying, I find that it *does* work, so my
testing must have been faulty.  My apologies.

>   SELECT E.*
>   FROM Employee AS E
>     LEFT JOIN Dept AS D USING (DeptName)
>   WHERE D.DeptName IS NULL

Thanks, I didn't know about USING.  Works fine, too:

$ cat ab-h.csv
a,b
a1,b1
a1,b2
a2,b1
a2,b2
$ cat bc-h.csv 
b,c
b1,c1
b1,c2
$ csvsql -e 'SELECT * FROM t1 LEFT JOIN t2 USING (b) WHERE t2.b IS NULL' ab-h.csv bc-h.csv
a,b,c
a1,b2,
a2,b2,
$

csvsql is a 82-line Perl script that reads its inputs with DBD::CSV,
assigns them to tables t1, t2, ..., performs the SQL query,
and writes the result with Test::CSV_XS (I didn't figure out how
to do it with DBD::CSV).  Quite an improvement over the Unix join.

> The key difference here is that the result of an antijoin is all of
> the columns of the source table and only those.

This really doesn't make much of a difference.
Sometimes I need one, sometimes the other.
 
> Reiner, you're probably better off to just use this SQL solution if you can.
> 
> Set::Relation is superfluous for this task actually.

I'd like to try it anyway,
but it won't install on my (Cygwin 1.7) system:

#   Failed test 'use Set::Relation;'
#   at t/Set_Relation_00_Compile.t line 9.
#     Tried to use 'Set::Relation'.
#     Error:  Can't use an undefined value as a HASH reference at
#     /usr/lib/perl5/site_perl/5.10/namespace/autoclean.pm

But my immediate problem is resolved.  Thanks to all!

> -- Darren Duncan

-- 
Reinier Post