with two conditions -- one compares a column from each
table and the other compares a column in the left table to
a constant value. The results should be equivalent to
putting the second comparison (to a constant) in a WHERE
clause and in the execution plan, the second comparison
appears in the WHERE condition of the join. The results
include rows that should have been filtered out.
Example:
select table1.A, table1.B, table2.A
from table1 left outer join table2
on table1.A=table2.A AND table1.B=1
table1
Row 1: A=1, B=1
Row 2: A=1, B=2
table2
Row 1: A=1, B=1
results
Row 1: 1, 1, 1
Row 2: 1, 2, <null>
The second row of the results should not be there. It was
also not joined with the row in table2.
(Is this the correct newsgroup for this question?)This is a multi-part message in MIME format.
--=_NextPart_000_0100_01C35B4D.9E3AD110
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
This is not a bug - it's a trap. If you subscribe to SQL Server =Professional, you can check out my article on this at:
http://www.pinnaclepublishing.com/SQ/SQmag.nsf/0/0F792E503632AF9A852568E2=
007AD8BC?open&login
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John VMT" <jbeckett@.vmtsoftware.com> wrote in message =news:0de601c35b6e$622b4940$a601280a@.phx.gbl...
In a SELECT query with a left join, I have an ON clause with two conditions -- one compares a column from each table and the other compares a column in the left table to a constant value. The results should be equivalent to putting the second comparison (to a constant) in a WHERE clause and in the execution plan, the second comparison appears in the WHERE condition of the join. The results include rows that should have been filtered out.
Example:
select table1.A, table1.B, table2.A
from table1 left outer join table2
on table1.A=3Dtable2.A AND table1.B=3D1
table1
Row 1: A=3D1, B=3D1
Row 2: A=3D1, B=3D2
table2
Row 1: A=3D1, B=3D1
results
Row 1: 1, 1, 1
Row 2: 1, 2, <null>
The second row of the results should not be there. It was also not joined with the row in table2.
(Is this the correct newsgroup for this question?)
--=_NextPart_000_0100_01C35B4D.9E3AD110
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
This is not a bug - it's a trap. =If you subscribe to SQL Server Professional, you can check out my article on =this at:
http://www.pinnaclepublishing.com/SQ/SQm=ag.nsf/0/0F792E503632AF9A852568E2007AD8BC?open&login=-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John VMT"
--=_NextPart_000_0100_01C35B4D.9E3AD110--|||Thanks for the quick feedback and correction. It's a bad
habit I got into several years ago with Jet databases (it
improved query performance).
It was unfortunate that the execution plan showed the
condition (column=<constant>) in the join's WHERE clause.
So the plan was as I expected but the execution was not.
Life is a series of disappointments when you hope for what
you want instead of what you get.
>--Original Message--
>This is not a bug - it's a trap. If you subscribe to SQL
Server Professional, you can check out my article on this
at:
>http://www.pinnaclepublishing.com/SQ/SQmag.nsf/0/0F792E503
632AF9A852568E2007AD8BC?open&login
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"John VMT" <jbeckett@.vmtsoftware.com> wrote in message
news:0de601c35b6e$622b4940$a601280a@.phx.gbl...
>In a SELECT query with a left join, I have an ON clause
>with two conditions -- one compares a column from each
>table and the other compares a column in the left table
to
>a constant value. The results should be equivalent to
>putting the second comparison (to a constant) in a WHERE
>clause and in the execution plan, the second comparison
>appears in the WHERE condition of the join. The results
>include rows that should have been filtered out.
>Example:
>select table1.A, table1.B, table2.A
>from table1 left outer join table2
>on table1.A=table2.A AND table1.B=1
>table1
>Row 1: A=1, B=1
>Row 2: A=1, B=2
>table2
>Row 1: A=1, B=1
>results
>Row 1: 1, 1, 1
>Row 2: 1, 2, <null>
>The second row of the results should not be there. It
was
>also not joined with the row in table2.
>(Is this the correct newsgroup for this question?)
>
No comments:
Post a Comment