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

On 05/07/10 14:38, Richard Quadling wrote:

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.

Clearly, or you'd know that they can't fly either... :) -- Peter Ford, Developer phone: 01580 893333 fax: 01580 893399 Justcroft International Ltd. www.justcroft.com Justcroft House, High Street, Staplehurst, Kent TN12 0AH United Kingdom Registered in England and Wales: 2297906 Registered office: Stag Gates House, 63/64 The Avenue, Southampton SO17 1XS