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
