sql server new users:

[quoted text, click to view]

This may just be intended to point out that inner join criteria are
interchangeable with WHERE-clause criteria. So something like:

select firstname + ' ' + lastname as name, gender, vacationhours as hours
from humanresources.employee e join person.contact c
on vacationhours>50
where gender='f' and e.contactid=c.contactid
order by hours desc

David

I am taking Microsoft Course 4329 on Joining Data from Multiple Tables in
SQL Server 2005, and I am stumped in the Lab, Task 2, Step 3.

How can I rewrite the following SQL Statement to produce the same results by
using a non-equality operator in the join?

use adventureworks
select firstname + ' ' + lastname as name, gender, vacationhours as hours
from humanresources.employee e join person.contact c on
e.contactid=c.contactid
where gender='f' and vacationhours>50
order by hours desc

Thanks in advance for your help!


--Justin--

Think of the JOIN conditions like filters. The difference between them and
the WHERE conditions has to do with logical timing (keep in mind that the
Optimizer can determine physical sequence any way it chooses as long as it
produces the same results).

The JOIN condition are pre-filter conditions, restricting which records are
considered for the join.

The WHERE conditions occur after the join between the tables, on the
resultant subset.

For INNER JOINS, it is immaterial as to whether the conditions are place in
the JOIN or the WHERE clauses. However, for OUTER (FULL, LEFT, or RIGHT),
timing is everything and will produce different results depending on which
clause you place the condition (again before or after the join actually
occurs); one subsets the tables before they are joined, the other restricts
the joined subset.

Sincerely,


Anthony Thomas


--

[quoted text, click to view]