List:General Discussion« Previous MessageNext Message »
From:Benjamin Pflugmann Date:June 7 2002 7:12pm
Subject:Re: sub-queries
View as plain text  

On Fri 2002-06-07 at 05:15:51 +0200, savage@stripped wrote:
> > No, it's probably not difficult at all. It simply seems as if
> > people do not understand exactly what you want. A communication
> > problem, IMHO.
> Kevin was also kind enough to point this out to me, and I have to
> agree with both of you.  As I did explain to Kevin in private
> however, I do have very

Never mind. I was just trying to explain why nobody came up with the
right solution at once.

> If anyone feel like taking 10 minutes of their time, and explaining

Well, probably more than 10 minutes. ;-) 

> exactly what the query does, I'll appreciate it allot.  It may help
> me understand the basic logic behind the structure of the query, and
> aid me in the future when I may need to execute such queries again
> (although, I'm honestly hoping that when such a time comes, MySQL
> will support sub-queries).

Okay. Let's see the query again, a bit reformatted:

SELECT mh.HostID, mh.HostDescription
FROM   monitorhosts mh
       LEFT JOIN monitorhostgroupdetails mhgd ON
	    mh.HostID = mhgd.HostID AND
	    mhgd.HostGroupID = 2
       mh.CompanyID = 1;

First, I assume that it is known that a normal JOIN (written with ',')
builds a cross product of the two tables, i.e. build pairs of each
record from the first table with each record of the second table.
Then, you normally have something like

  WHERE mh.HostID = mhgd.HostID

which only chooses those pairs, which have matching HostIDs. A LEFT
JOIN does the same, but for all records of the left (=first) table,
which have no match in the right table, it will insert NULL for the
right table values. I.e. if you have (from above)

  LEFT JOIN ... ON mh.HostID = mhgd.HostID

you will get the result from a normal JOIN (all pairs for which
mh.HostID = mhgd.HostID is true) and all remaining records from
monitorhosts (all for whose HostID was no record in
monitorhostgroupdetails) paired with NULL values for the columns of

Another way to see this is to take all records from the left table and
pair them with either the matching records from the right table or
with NULL values if record matched.

An additional "mhgd.HostGroupID = 2" in the ON clause will only
consider a pair valid, if "mhgd.HostGroupID = 2" (as in a normal join)
and for all non-fitted records of the left table it pairs them with
NULL values again. This means, we now get NULL values for all (former)
pairs which have "mhgd.HostGroupID != 2".

Now the WHERE clause can be applied. "mhgd.HostID IS NULL" now chooses
all records, which have NULL values for the right table, i.e. all
pairs, which had no match on the condition "mh.HostID = mhgd.HostID
AND mhgd.HostGroupID = 2", this means all records of the left table,
for which there was no matching HostID in mhgd which also was in
mhgd.HostGroupID = 2. The latter is a different wording for

... mh.HostID NOT IN ( SELECT mhgd.HostID
	               FROM monitorhostgroupdetails mhgd
		       WHERE mhgd.HostGroupID = 2 )

Which should look familiar to you. ;-)

"mh.CompanyID = 1" restricts the result to only the company in
question, of course.

Of course, the RDBMS (here MySQL) optimizes how it retrieves the pairs
you want. But the above is the underlying logic of how it works.

Hope that helped,


sub-queriesChris Knipe6 Jun
  • Re: sub-queriesKiss D├íniel6 Jun
    • Re: sub-queriesChris Knipe6 Jun
      • RE: sub-queriesCal Evans6 Jun
  • Re: sub-queriesSabine Richter6 Jun
  • Re: sub-queriesChris Knipe6 Jun
  • Re: sub-queriesChris Knipe6 Jun
    • RE: sub-queriesKevin Fries7 Jun
      • Re: sub-queriesChris Knipe7 Jun
        • Re: sub-queriesBenjamin Pflugmann7 Jun
      • Re: sub-queriesChris Knipe7 Jun
        • Re: sub-queriesBenjamin Pflugmann7 Jun