Asp Forum - Non-Equi Joins - microsoft.public.sqlserver.programming

Justin Ryan Grenier

3/22/2007 7:45:00 PM

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--

10 Answers

David Browne

3/22/2007 8:22:00 PM

"Justin Ryan Grenier" <JustinGrenier@AESeducation.com> wrote in message
news:5IydnUKr8o1MQZ_bnZ2dnUVZ_hynnZ2d@suscom.com...
>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!
>
>

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

Anthony Thomas

3/23/2007 3:20:00 AM

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

--

"Justin Ryan Grenier" <JustinGrenier@AESeducation.com> wrote in message
news:5IydnUKr8o1MQZ_bnZ2dnUVZ_hynnZ2d@suscom.com...
> 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--
>
>

Hugo Kornelis

3/23/2007 11:13:00 PM

On Thu, 22 Mar 2007 15:21:43 -0500, "David Browne" <davidbaxterbrowne no
potted meat@hotmail.com> wrote:

>
>
>"Justin Ryan Grenier" <JustinGrenier@AESeducation.com> wrote in message
>news:5IydnUKr8o1MQZ_bnZ2dnUVZ_hynnZ2d@suscom.com...
>>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!
>>
>>
>
>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

Hi David,

Though this answer pprobably is what the course is looking for, I find
both the question and the answer extremely ill advised.

Common best practise is to always write the actual join criteria in the
join, and apply the filters in the where. (The first part of this advise
is unanimous; the second part is given by a large majority, but there
are some exceptions).

Moving the filters to the join clause and the join predicate to the
where clause technicallly answers the question, but it makes the query a
lot harder to understand and maintain. And since the optimizer is free
to reorganize the order of the predicates anyway (as long as the outcome
doesn't change), there is no advantage in rewriting it this way.

The author of the Microsoft Course deserves a thoroughh slapping for
including this question!

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...

Steve Dassin

3/26/2007 8:45:00 PM

I rarely agree with anyome here...but this is a pleasant exception -:)

"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message
news:7on803h2o86v1g6a68o470rdarmli1dit6@4ax.com...
> On Thu, 22 Mar 2007 15:21:43 -0500, "David Browne" <davidbaxterbrowne no
> potted meat@hotmail.com> wrote:
>
>>
>>
>>"Justin Ryan Grenier" <JustinGrenier@AESeducation.com> wrote in message
>>news:5IydnUKr8o1MQZ_bnZ2dnUVZ_hynnZ2d@suscom.com...
>>>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!
>>>
>>>
>>
>>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
>
> Hi David,
>
> Though this answer pprobably is what the course is looking for, I find
> both the question and the answer extremely ill advised.
>
> Common best practise is to always write the actual join criteria in the
> join, and apply the filters in the where. (The first part of this advise
> is unanimous; the second part is given by a large majority, but there
> are some exceptions).
>
> Moving the filters to the join clause and the join predicate to the
> where clause technicallly answers the question, but it makes the query a
> lot harder to understand and maintain. And since the optimizer is free
> to reorganize the order of the predicates anyway (as long as the outcome
> doesn't change), there is no advantage in rewriting it this way.
>
> The author of the Microsoft Course deserves a thoroughh slapping for
> including this question!
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hug...

AK

3/26/2007 10:12:00 PM

On Mar 26, 3:44 pm, "Steve Dassin" <s...@nospamrac4sql.net> wrote:
> I rarely agree with anyome here...but this is a pleasant exception -:)
>
> "Hugo Kornelis" <h...@perFact.REMOVETHIS.info.INVALID> wrote in message
>
> news:7on803h2o86v1g6a68o470rdarmli1dit6@4ax.com...
>
>
>
> > On Thu, 22 Mar 2007 15:21:43 -0500, "David Browne" <davidbaxterbrowne no
> > potted m...@hotmail.com> wrote:
>
> >>"Justin Ryan Grenier" <JustinGren...@AESeducation.com> wrote in message
> >>news:5IydnUKr8o1MQZ_bnZ2dnUVZ_hynnZ2d@suscom.com...
> >>>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!
>
> >>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
>
> > Hi David,
>
> > Though this answer pprobably is what the course is looking for, I find
> > both the question and the answer extremely ill advised.
>
> > Common best practise is to always write the actual join criteria in the
> > join, and apply the filters in the where. (The first part of this advise
> > is unanimous; the second part is given by a large majority, but there
> > are some exceptions).
>
> > Moving the filters to the join clause and the join predicate to the
> > where clause technicallly answers the question, but it makes the query a
> > lot harder to understand and maintain. And since the optimizer is free
> > to reorganize the order of the predicates anyway (as long as the outcome
> > doesn't change), there is no advantage in rewriting it this way.
>
> > The author of the Microsoft Course deserves a thoroughh slapping for
> > including this question!
>
> > --
> > Hugo Kornelis, SQL Server MVP
> > My SQL Server blog:http://sqlblog.com/blogs/hugo... Hide quoted text -
>
> - Show quoted text -

Yes, that's an excellent explanation! Just for the record: it applies
to _inner_ joins only. For outer joins the rules are slightly
different, right?

Steve Dassin

3/27/2007 12:03:00 AM

Hugo's wise advise:

'Common best practise is to always write the actual join criteria in the
join, and apply the filters in the where.'

applied to outer joins would be much clearer to non sql experts were it
not for the ability to put filters in ON.

The article you recommended on outer joins:
Meet the experts: Terry Purcell on coding predicates in outer joins:
A comparison of simple outer join constructs
http://www-128.ibm.com/developerworks/db2/library/techarticle/purcell/0112pu...

implies the author was concerned with the implications of the new
ability of filters in ON. Without this ability the only 2 choices
would be a derived table to express the filter (assuming the null
supplied table) and the WHERE statement after the join. In this
context Hugo makes much more sense as to the implications of each
method. I think the author was aware of this and the only advantage
of the 'shorthand' mixing of WHERE filters in ON is at the alter
from which all else springs: performance -:) There is simply more
clarity without this shorthand.
To digress a moment, every sql addition is a 50/50 proposition.
ON/WHERE is a conceptual negitive, CASE for chararactisitic functions
a conceptual big plus and while APPLY is a big plus it leaves the
subquery as part of the language not unlike HAVING vs. WHERE.
Oh well :)

best,
steve

"Alex Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message
news:1174947142.740082.242050@n76g2000hsh.googlegroups.com...
> On Mar 26, 3:44 pm, "Steve Dassin" <s...@nospamrac4sql.net> wrote:
> > I rarely agree with anyome here...but this is a pleasant exception -:)
> >
> > "Hugo Kornelis" <h...@perFact.REMOVETHIS.info.INVALID> wrote in message
> >
> > news:7on803h2o86v1g6a68o470rdarmli1dit6@4ax.com...
> >
> >
> >
> > > On Thu, 22 Mar 2007 15:21:43 -0500, "David Browne" <davidbaxterbrowne
no
> > > potted m...@hotmail.com> wrote:
> >
> > >>"Justin Ryan Grenier" <JustinGren...@AESeducation.com> wrote in
message
> > >>news:5IydnUKr8o1MQZ_bnZ2dnUVZ_hynnZ2d@suscom.com...
> > >>>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!
> >
> > >>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
> >
> > > Hi David,
> >
> > > Though this answer pprobably is what the course is looking for, I find
> > > both the question and the answer extremely ill advised.
> >
> > > Common best practise is to always write the actual join criteria in
the
> > > join, and apply the filters in the where. (The first part of this
advise
> > > is unanimous; the second part is given by a large majority, but there
> > > are some exceptions).
> >
> > > Moving the filters to the join clause and the join predicate to the
> > > where clause technicallly answers the question, but it makes the query
a
> > > lot harder to understand and maintain. And since the optimizer is free
> > > to reorganize the order of the predicates anyway (as long as the
outcome
> > > doesn't change), there is no advantage in rewriting it this way.
> >
> > > The author of the Microsoft Course deserves a thoroughh slapping for
> > > including this question!
> >
> > > --
> > > Hugo Kornelis, SQL Server MVP
> > > My SQL Server blog:http://sqlblog.com/blogs/hugo... Hide quoted
text -
> >
> > - Show quoted text -
>
> Yes, that's an excellent explanation! Just for the record: it applies
> to _inner_ joins only. For outer joins the rules are slightly
> different, right?
>

Hugo Kornelis

3/27/2007 9:49:00 PM

On 26 Mar 2007 15:12:22 -0700, Alex Kuznetsov wrote:

>Yes, that's an excellent explanation! Just for the record: it applies
>to _inner_ joins only. For outer joins the rules are slightly
>different, right?

Hi Alexander,

Yes, that's right. For an outer join, filters added to the ON clause are
applied before the outer join is applied, and filters in the WHERE
clause afterwards (that is, after "rows" with only NULL values are added
to the outer table for unmatched rows in the inner table).

Though it's possible and legal to include only a filter in the ON clause
and move the join condition to the WHERE clause, the result will be an
unmaintainable mess. Plus, it wouldn't be a real join at all.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...

Steve Dassin

3/27/2007 10:48:00 PM

"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message
news:ov3j03l7cg7pr3j7btmir8bo0pie7logkk@4ax.com...
> Though it's possible and legal to include only a filter in the ON clause
> and move the join condition to the WHERE clause, the result will be an
> unmaintainable mess.

Sir I believe that was my point. This is precisely a result of sql
not distinguishing between exactly what is logically a join condition
and what is logically a where condition. Clarity is sacrificed for a
potential performance gain.

> Plus, it wouldn't be a real join at all.

Sir you are wrong. Sql does consider it a 'legal' join and that
is the problem!

I am an advocate of the Dataphor relational system @
www.alphora.com

www.beyondsql.blogspot.com

Hugo Kornelis

3/28/2007 9:11:00 PM

On Tue, 27 Mar 2007 15:47:56 -0700, Steve Dassin wrote:

>"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message
>news:ov3j03l7cg7pr3j7btmir8bo0pie7logkk@4ax.com...
>> Though it's possible and legal to include only a filter in the ON clause
>> and move the join condition to the WHERE clause, the result will be an
>> unmaintainable mess.
>
>Sir I believe that was my point. This is precisely a result of sql
>not distinguishing between exactly what is logically a join condition
>and what is logically a where condition. Clarity is sacrificed for a
>potential performance gain.

Honorable Master Dassin,

The performance gain is achieved by the optimizer, that will consider
alternate ways to phrase the same query. The SQL language does in fact
distinguish between join predicates and where filters, except that the
langauge specification allows programmers to mix them. Which in the case
of an outer join is actually a good thing, since it makes some queries
more readable than the alternative (which would involve a common table
expression, a derived table or a seperate view).

>> Plus, it wouldn't be a real join at all.
>
>Sir you are wrong. Sql does consider it a 'legal' join and that
>is the problem!

SQL considers it a valid query, since it passes the syntax check and the
ANSI standard allows it. That doesn't make it a join.

Let's for example examine this query:

<Warning, bad code>

SELECT blah
FROM table1
LEFT OUTER JOIN table2 ON table2.col2 = 3
WHERE table1.col1 = table2.col1;

</Warning, bad code>

Now, there are basically two cases. Case one happens if table2 has at
least one row with col2 equal to 3. The join pairs each row in table1
with each row with col2 = 3 in table2; the where filter than removes
pairs with unequal col1. The end result is the same as from this query:

SELECT blah
FROM table1
INNER JOIN table2 ON table1.col1 = table2.col1
WHERE table2.col2 = 3;

So in THIS case, the malformed outer join does look like a join. But
what if there's no row in table2 with col2 = 3? In that case, the outer
join returns all rows from table1, all with NULLS in the columns from
table2. Since table2.col1 is one of those columns, none of the rows in
the result will pass the WHERE clause, and the result set is empty.

Hmmm. That's also the same result as the inner join above would return.
Interesting!

Next: what happens if we change the LEFT join in the bad query above to
a RIGHT join? That one looks even more interesting!
* Each row in table2 with col2 = 3 will be paired with each row from
table1. The WHERE filter will then remove pairs with unequal rows.
* Each row in table2 with col2 <> 3 will be once in the result of the
join, with a bunch of NULLS for table1's columns. The WHERE clause will
swiftly remove this row.
So the end result is, again, the same as the INNER JOIN query above!

Which brings me to admitting I was wrong. It *would* be a real join. In
fact, it would be a real *inner* join, cleverly concealed as an outer
join. Thanks for forcing me to give this some more thought than I had
done at first.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...

Steve Dassin

3/29/2007 8:38:00 AM

Hello Hugo,

> In fact, it would be a real *inner* join, cleverly concealed as an outer
> join.

And you have solved the riddle of
Q: "When is a door not a door"?
A: "When it's a jar".

Borrowing from your example, playing around with this query one sees a
concealed cross join.

select a.OrderID,a.CustomerID,a.EmployeeID,a.freight,b.orderid,b.productid
from orders as a
--inner join
left join [order details] as b on 1<2
--where a.orderid=10248

There are endless examples of such peek-a-boo. Now I readily concede
that server supports this type of query as a consequence of
allowing predicate(s) in ON. But logically it is a wart, a
contradiction in terms. How can it possibly be that I can define
a join without relating the columns in the tables!? This, to me,
is logically absurd. And the concealment, the transformation if
you will, is how this absurdity resolves itself in the product.
At the risk of being called looney, a thing is or a thing isn't.
A thing, a left join, a right join, an inner join should have
a logical definition. How can you give a definition of a left
join that the above example illustrates? How can it be logically
justified that a query like this even compiles? Where is the
question it is an answer too?
Perhaps someone will counter that it's 'somewhat' a left join.
I've been down that garden path with several knowledge people here,
as in somewhat 'relational'. As in somewhat pregnant:).
Certainly it is easy to substitute the 50 million lines of code
in the optimizer for any kind of body of knowledge/theory. (A point,
I might add, that I have tried to make with Ms. Delaney). But for
some that comes at way too high a price. Your own exercise
shows a more rigorous logic than the product you were investigating!
I wonder, my friend, when you will reach your threshold? -:)

very best,
steve

"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message
news:abll03t307dslda8nrjtr09ade90sgiotv@4ax.com...
> On Tue, 27 Mar 2007 15:47:56 -0700, Steve Dassin wrote:
>
> >"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message
> >news:ov3j03l7cg7pr3j7btmir8bo0pie7logkk@4ax.com...
> >> Though it's possible and legal to include only a filter in the ON
clause
> >> and move the join condition to the WHERE clause, the result will be an
> >> unmaintainable mess.
> >
> >Sir I believe that was my point. This is precisely a result of sql
> >not distinguishing between exactly what is logically a join condition
> >and what is logically a where condition. Clarity is sacrificed for a
> >potential performance gain.
>
> Honorable Master Dassin,
>
> The performance gain is achieved by the optimizer, that will consider
> alternate ways to phrase the same query. The SQL language does in fact
> distinguish between join predicates and where filters, except that the
> langauge specification allows programmers to mix them. Which in the case
> of an outer join is actually a good thing, since it makes some queries
> more readable than the alternative (which would involve a common table
> expression, a derived table or a seperate view).
>
> >> Plus, it wouldn't be a real join at all.
> >
> >Sir you are wrong. Sql does consider it a 'legal' join and that
> >is the problem!
>
> SQL considers it a valid query, since it passes the syntax check and the
> ANSI standard allows it. That doesn't make it a join.
>
> Let's for example examine this query:
>
> <Warning, bad code>
>
> SELECT blah
> FROM table1
> LEFT OUTER JOIN table2 ON table2.col2 = 3
> WHERE table1.col1 = table2.col1;
>
> </Warning, bad code>
>
> Now, there are basically two cases. Case one happens if table2 has at
> least one row with col2 equal to 3. The join pairs each row in table1
> with each row with col2 = 3 in table2; the where filter than removes
> pairs with unequal col1. The end result is the same as from this query:
>
> SELECT blah
> FROM table1
> INNER JOIN table2 ON table1.col1 = table2.col1
> WHERE table2.col2 = 3;
>
> So in THIS case, the malformed outer join does look like a join. But
> what if there's no row in table2 with col2 = 3? In that case, the outer
> join returns all rows from table1, all with NULLS in the columns from
> table2. Since table2.col1 is one of those columns, none of the rows in
> the result will pass the WHERE clause, and the result set is empty.
>
> Hmmm. That's also the same result as the inner join above would return.
> Interesting!
>
> Next: what happens if we change the LEFT join in the bad query above to
> a RIGHT join? That one looks even more interesting!
> * Each row in table2 with col2 = 3 will be paired with each row from
> table1. The WHERE filter will then remove pairs with unequal rows.
> * Each row in table2 with col2 <> 3 will be once in the result of the
> join, with a bunch of NULLS for table1's columns. The WHERE clause will
> swiftly remove this row.
> So the end result is, again, the same as the INNER JOIN query above!
>
> Which brings me to admitting I was wrong. It *would* be a real join. In
> fact, it would be a real *inner* join, cleverly concealed as an outer
> join. Thanks for forcing me to give this some more thought than I had
> done at first.
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hug...

microsoft.public.sqlserver.programming

Non-Equi Joins

Justin Ryan Grenier

David Browne

Anthony Thomas

Hugo Kornelis

Steve Dassin

AK

Steve Dassin

Hugo Kornelis

Steve Dassin

Hugo Kornelis

Steve Dassin

x Login to ForumsZone