A Query on Outer Join.

Hi.

I want to know the difference between taking an outer join on tables and taking an outer join on the conditions that are defined between the tables.

Thanks in Advance.

outer join on tables :
A join that includes all the rows from the joined tables that have met the search conditions, even rows from one table for which there is no matching row in the other join table. For result set rows returned when a row in one table is not matched by a row from the other table, a value of NULL is supplied for all result set columns that are resolved to the table that had the missing row.

outer join on the conditions :

Join conditions can be specified in either the FROM or WHERE clauses; specifying them in the FROM clause is recommended. WHERE and HAVING clauses can also contain search conditions to further filter the rows selected by the join conditions.

|||

Hi.

Thanks a lot for the reply.

I got about the outer join that is performed on tables.But, the outer join on the conditions is still confusing.

Can you help me with an example so that i can understand the concept in a better way.

Also, when and why outer joins on the conditions are used.Is it better than outer join that is performed on tables.I am really confused.

I request you to help me out from this confusion.

Thanks in Advance.

|||

The difference between specifying an outer join in the FROM clause and the WHERE clause has to do with when the conditions are applied. Conditions specified in the WHERE clause are applied after the JOIN has happened. This means that the whole record will be excluded instead of just one half.

Consider a report of employees and any car that is not red.

Select e.name, c.model, c.color

From employees e

Left Join cars c

on e.empID = c.empID

and c.color <> 'RED'

Select e.name, c.model, c.color

From employeess e,

cars c

Where e.empID *= c.empID

And c.color <> 'RED'

The second one will not display employees that only have a red car.

|||

This seems a little confusing. Please do not mix ANSI syntax and legacy join syntax, since they don't work the same internally.
The explanation is correct, but the examples may be confusing, since the latter, old legacy syntax should be avoided and never used at all. (for one thing there is no separation between the join condition and the were clause in it)

A better example may be:

Select e.name, c.model, c.color
From employees e
Left Join cars c
on e.empID = c.empID
AND c.color <> 'RED'

Select e.name, c.model, c.color
From employees e
Left Join cars c
on e.empID = c.empID
WHERE c.color <> 'RED'

To apply this on something that most already have, we can use Northwind.
Just try out both and look at the results, and I think that it should be fairly easy to understand the differences.
The main thing is that the filter we are playing with is applied to the inner table, that's when it makes a difference if the filter goes into the ON clause or the WHERE clause.
As said earlier, the WHERE filters is applied after the join is formed, where filtering in the ON clause does affect how the join is formed. (for the example below, compare which homepages is included or excluded from what is available with the different examples)

So, to the original poster, it comes down to what question you want to have an answer to, if your filter should be in the ON or WHERE clause.
In addition, this is 'only' important when the filter is to be applied to the inner table, filtering on columns in the outer table doesn't produce different results regardless of if it's in the ON or WHERE clause.

use northwind
go
select p.productname, p.unitprice, s.companyName, s.HomePage
from products p
left join suppliers s
on s.supplierID = p.supplierID
and s.HomePage like '#%'
go
select p.productname, p.unitprice, s.companyName, s.HomePage
from products p
left join suppliers s
on s.supplierID = p.supplierID
where s.HomePage like '#%'
go

Compare the results from the above, and you'll also see that the latter is behaving excactly as an inner join, which, if someone was to write the query, probably wouldn't be their intention.
Also, you can compare with the results of the entire join except the last and/where line to see which homepages that is included/excluded due to filtering, and how that affects what is or isn't included from the inner table, and how the outer table is filtered.

Hope it didn't get too confusing..?
=;o)
/Kenneth

|||Of course, the initial question asked the difference between outer joining on Tables and outer joining on conditions. If that does not refer to the older syntax, then I'm not sure how to interpret it.|||

Hi KeWin.

Thanks a lot.