Re: Inner join woes... and sweet tea!

On 5 July 2010 14:02, Jason Pruim <lists@pruimphotography.com> wrote: > Hi everyone, > > I'll admit right now that I'm still trying to wrestle with inner joins... It is all about set theory. Imagine two circles, which overlap (http://en.wikipedia.org/wiki/Venn_diagram#Example as an example). For that example, simplistically, A contains me and my emu. B contains my emu and the my deathwatch beetle. SELECT * FROM A,B WHERE A.id = B.id (My emu) SELECT * FROM A INNER JOIN B ON A.id = B.id (My emu) SELECT * FROM A LEFT OUTER JOIN B ON A.id = B.id (Me and My emu) SELECT * FROM A RIGHT OUTER JOIN B ON A.id = B.id (My emu and my deathwatch beetle) SELECT * FROM A FULL OUTER JOIN B ON A.id = B.id returns in interesting set (essentially all things but 1 column for each table). Me, null My emu, my emu null, My deathwatch beetle. If you were using ISNULL ... SELECT ISNULL(A.name, B.name) AS name FROM A FULL OUTER JOIN B ON A.id = B.id would return all things Me My emu My deathwatch beetle. And, (I think), finally, an inversion of the inner join. SELECT ISNULL(A.name, B.name) AS name FROM A FULL OUTER JOIN B ON A.id = B.id WHERE A.id IS NULL OR B.id IS NULL returns Me My deathwatch beetle. All things except those 2 legged things that can fly. I hope that helps. Regards, Richard. P.S. I don't have an emu.