2010/10/4 Reinier Post <r...@win.tue.nl>: > 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
