[thelist] RE: SQL - HAVING and OUTER LEFT JOIN

[John.Brooking at sappi.com]

> With regards to SQL, can someone explain to me in simple terms what the 
> HAVING clause does, and what an OUTER LEFT JOIN does?

Chris,

   You've probably gotten some other replies by now, since I get the digest
version, but I'll take a stab at a short and simple explanation anyway.

   Types of joins: INNER, OUTER LEFT, and OUTER RIGHT.

   Imagine your two tables side by side, as in a visual query tool. An INNER
JOIN is where a record exists in *both* tables that you are joining. An
OUTER JOIN in general is where a record exists in *one or both*. OUTER LEFT
is where it exists in the "left" or both, OUTER RIGHT is where it exists in
the "right" or both.

   Another way of visualizing it is by "Venn diagram". Two overlapping
circles represent your tables. In an INNER JOIN, shade just the
intersection; those are the records selected. In an OUTER LEFT, shade the
entire left circle, including the intersection. For OUTER RIGHT, the entire
right circle, including intersection.

   I won't get into join syntax, since that can vary, and I don't know what
DB you are using. (And might not be familiar with the one that you are.)

   HAVING is pretty much a separate issue from joins. It can be used when
you are grouping things. Say, you want to see how many widgets of each color
you have. You could "SELECT color, COUNT(*) FROM widgets GROUP BY color". As
you probably know, the GROUP BY is necessary when you use grouping
functions, as it tells SQL what you want to, well, group by.

   HAVING is sort of like a WHERE, except it applies to one of the
summarized fields, after the grouping is done. WHERE is applied to the
source records before grouping. So, maybe you want to see all non-white
widgets for which your inventory is getting low:

    SELECT color, COUNT(*)
    FROM widgets
    WHERE color <> 'white'       -- include only non-white widgets
    GROUP BY color               -- group results by color
    HAVING COUNT(*) < 10         -- show only colors with less than 10 left

   Should be pretty self-explanatory, with the comments (which line up
nicely in non-proportional font, in case you aren't seeing it that way).
Again, HAVING selects records based on summarized fields after the grouping;
WHERE selects records based on non-summarized fields before the grouping.

   Hope this helps, and is not too redundant to previous replies I haven't
seen yet.

- John

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

This message may contain information which is private, privileged or
confidential and is intended solely for the use of the individual or entity
named in the message. If you are not the intended recipient of this message,
please notify the sender thereof and destroy / delete the message. Neither
the sender nor Sappi Limited (including its subsidiaries and associated
companies) shall incur any liability resulting directly or indirectly from
accessing any of the attached files which may contain a virus or the like.