Re: JOINs verses ='s ?

Hi bpfh,

Other people will give you a good explanation of how outer joins work. I'd like to answer a different question, namely, what are they for?

Let's say you have a list of salesman, and you are making a report on the sales per month made by each salesman. To make this report, you need to join the salesman table to the sales table. The sales table has salesman_id as a foreign key, so there's the join condition.

Now let's say that some poor hapless salesman had absolutely no sales at all during the entire month! Well, if we do a regular inner join between salesmen and sales, our poor unsuccessful salesman will drop out of the report entirely! There won't be any rows over in the sales table that match the salesman_id for our hero, and also meet the criteria for the given month.

Well, our salesman might actually prefer to be quietly left off the report, but the people who read the report want to see some thin like:

Salesman Jones

• no sales ****

An outer join will let you achieve that result. Now, with this example, you can follow the explanation that others will give as to how it works.

Note: in my experience, the syntax for outer joins is different for different variants of SQL.

--
Regards,
    David Cressey
    www.dcressey.com