Re: anti-join with SQL::Statement?

2010/10/4 Reinier Post <[email protected]>:
> 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).

In theory following should work:

$dbh->do( "CREATE TABLE tgt_tbl AS SELECT * FROM t1 LEFT JOIN t2 USING
(b) WHERE t2.b IS NULL" );

I can't give any guarantees, because I haven't tried it (currently
fighting with corrupted filesystems on my development box).

Have a look to t/16morejoins.t - there're some buggy behavior of
SQL::Statement using left joins. Be careful.

>  Quite an improvement over the Unix join.

Glad to hear it :)

/Jens