On Thu, Jun 9, 2011 at 6:17 AM, James David Smith
<james.david.smith@gmail.com> wrote:
> Hi Merlin,
>
> Thank you very much for the full and brilliant reply. The last query
> you wrote does exactly what I want it too. I wonder whether it's not
> too much trouble whether you could explain to me a couple of things
> though...?
>
> 1) I understand what you are saying my query does. Like an outer join
> I think? But don't get why. Using '!=' is the opposite of '=' is it
> not?
It is the opposite -- just not in the way you are thinking. In SQL,
joins between tables means 'give me every combination of data from
table A combined with table B given a condition'. If A and B each
have 100 records with identifiers 1-100, the not equal join would give
you a join result of 99 records for A=1 (with B 2-100), 99 records for
A=2 (with B 1, 3-100) etc. for a total of 9900 records.
Your problem is that you are still associating tables A and B in your
head in a way that is not expressed in the join. You have to imagine
both tables as pools of unassociated records with no ordering except
for what you give in the query.
> 2) I understand the 'where not exists' query you suggest, and have
> used that, but I don't see why you use '1' in it. What does the 1 do?
where not exists means 'return this record if this query does not
return at least 1 record' -- since we don't care what is in the record
that is actually returned, I just use 1 as a shorthand because in SQL
you can't write queries that return 0 fields -- this is a pretty
common convention. I instead of 1, I could have used *, -999, or
anything -- it doesn't matter.
Aside: exists/not exists in relational parlance are called 'semi
joins'. They can often be faster than regular joins because the
server can bail early without having to express the full join.
merlin