I have uncovered some interesting techniques that I wanted to share
with everyone. In particular, they relate to the claimed "ambiguity"
between using the ANSI outer join operators (LEFT OUTER JOIN, RIGHT
OUTER JOIN) over *= syntax by Microsoft. A little background would be
helpful. Microsoft has stated that it will not be supporting the
older *= syntax into the future. The claim is that it poses ambiguity
when adding filter logic to the left outer join query. If the filter
logic is added to the FROM clause, the LEFT OUTER JOIN is preserved.
In contrast, filter logic added to the WHERE clause actually turns a
left outer join into an inner join. Example:
CREATE TABLE #foo (indx INT IDENTITY(1,1), taste VARCHAR(255));
CREATE TABLE #bar (indx INT IDENTITY(1,1), taste VARCHAR(255));
INSERT INTO #foo (taste) VALUES ('yummy!');
INSERT INTO #foo (taste) VALUES ('bummy!');
INSERT INTO #foo (taste) VALUES ('mummy!');
INSERT INTO #bar (taste) VALUES ('yummy!');
INSERT INTO #bar (taste) VALUES ('tummy!');
INSERT INTO #bar (taste) VALUES ('mummy!');
--This will return all records from dbo.foo with matching records
from
--dbo.bar filtered by b.COL1 = "yummy!" non matching dbo.bar records
will
--produce NULLS.
SELECT
*
FROM
#foo f
LEFT OUTER JOIN #bar b ON b.taste = f.taste AND
b.taste = 'yummy!'
indx taste indx taste
-- -- -- --
1 yummy! 1 yummy!
2 bummy! NULL NULL
3 mummy! NULL NULL
-- This will only return records from dbo.foo that have a matching
-- dbo.bar record where b.COL1 = "yummy!" In other words, this
produces
-- an inner join styled "filter"
SELECT
*
FROM
#foo f
LEFT OUTER JOIN #bar b ON b.taste = f.taste
WHERE
b.taste = 'yummy!'
indx taste indx taste
-- -- -- --
1 yummy! 1 yummy!
This illustration highlights the fact that filter logic in the FROM
clause is applied before the join is made while filter logic in the
WHERE clause is applied after the join. This is a characteristic of
Microsoft's preferred syntax. With the older SQL 92 syntax, we have a
different flavor. The logic in the WHERE clause also preserves our
LEFT outer join with the addition of filters. Example:
SELECT
*
FROM
#foo f,
#bar b
WHERE
f.taste *= b.taste
AND b.taste = 'yummy!'
indx taste indx taste
-- -- -- --
1 yummy! 1 yummy!
2 bummy! NULL NULL
3 mummy! NULL NULL
In my opinion, there is no ambiguity at all. If you are familiar with
the peculiarities of these two competing styles, you can get the
results you want. Certainly the query and result themselves are not
ambiguous to the system. Now, since it is sometimes desirable in
practice to be able to apply filter logic to a relation *after* two
tables have been left outer joined (in particular, in WHERE clause
subqueries), I was curious to test the limits of MS claim. What I
found is that you can have WHERE clause filter logic with ANSI outer
join operators but with a twist. The trick is to add an additional
filter which includes NULL values from your joined table. Example:
SELECT
*
FROM
#foo f
LEFT OUTER JOIN #bar b ON b.taste = f.taste
WHERE
b.taste = 'yummy!' OR
b.taste IS NULL
indx taste indx taste
-- -- -- --
1 yummy! 1 yummy!
2 bummy! NULL NULL
Here we get a different result. Tuples matching my filter value
'yummy!' are included as well as tuples that have no matching value
and produce NULL. The twist is rows that do have matching values in
#bar that are not filtered for (i.e. 'mummy!') are excluded. Also,
there is an addition drawback. If #.bar COL1 has an index defined on
it, the system will perform an index scan as opposed to a seek.
Nevertheless, this approach can be a significant improvement on an
order of magnitude better if the only alternative is line - by -line
processing.
As of this writing, older SQL 92 syntax is unsupported in SQL Server
2005 level 9.0. With MS's history of dealing with competing
technologies, I suspect there is more than meets the eye with
Microsoft's witch hunt against *= syntax beyond the claim of ambiguity
but I digress.manstein,
I must admit that I did not thoroughly read your posting.
However, the "supposed" ambiguity of the old outer join syntax (such as
*=) is well known and documented. Here is one posting that illustrates
the ambiguity, and highlights this by sharing with the rest of the world
how different RDBMS vendors (at the time) had implemented this syntax:
differently! The posting almost dates back to the last century.
See
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/518d849c9b1040c0?dmode=source
(url may warp)
Gert-Jan
manstein wrote:
> I have uncovered some interesting techniques that I wanted to share
> with everyone. In particular, they relate to the claimed "ambiguity"
> between using the ANSI outer join operators (LEFT OUTER JOIN, RIGHT
> OUTER JOIN) over *= syntax by Microsoft. A little background would be
> helpful. Microsoft has stated that it will not be supporting the
> older *= syntax into the future. The claim is that it poses ambiguity
> when adding filter logic to the left outer join query. If the filter
> logic is added to the FROM clause, the LEFT OUTER JOIN is preserved.
> In contrast, filter logic added to the WHERE clause actually turns a
> left outer join into an inner join. Example:
> CREATE TABLE #foo (indx INT IDENTITY(1,1), taste VARCHAR(255));
> CREATE TABLE #bar (indx INT IDENTITY(1,1), taste VARCHAR(255));
> INSERT INTO #foo (taste) VALUES ('yummy!');
> INSERT INTO #foo (taste) VALUES ('bummy!');
> INSERT INTO #foo (taste) VALUES ('mummy!');
> INSERT INTO #bar (taste) VALUES ('yummy!');
> INSERT INTO #bar (taste) VALUES ('tummy!');
> INSERT INTO #bar (taste) VALUES ('mummy!');
> --This will return all records from dbo.foo with matching records
> from
> --dbo.bar filtered by b.COL1 = "yummy!" non matching dbo.bar records
> will
> --produce NULLS.
> SELECT
> *
> FROM
> #foo f
> LEFT OUTER JOIN #bar b ON b.taste = f.taste AND
> b.taste = 'yummy!'
> indx taste indx taste
> -- -- -- --
> 1 yummy! 1 yummy!
> 2 bummy! NULL NULL
> 3 mummy! NULL NULL
> -- This will only return records from dbo.foo that have a matching
> -- dbo.bar record where b.COL1 = "yummy!" In other words, this
> produces
> -- an inner join styled "filter"
> SELECT
> *
> FROM
> #foo f
> LEFT OUTER JOIN #bar b ON b.taste = f.taste
> WHERE
> b.taste = 'yummy!'
> indx taste indx taste
> -- -- -- --
> 1 yummy! 1 yummy!
> This illustration highlights the fact that filter logic in the FROM
> clause is applied before the join is made while filter logic in the
> WHERE clause is applied after the join. This is a characteristic of
> Microsoft's preferred syntax. With the older SQL 92 syntax, we have a
> different flavor. The logic in the WHERE clause also preserves our
> LEFT outer join with the addition of filters. Example:
> SELECT
> *
> FROM
> #foo f,
> #bar b
> WHERE
> f.taste *= b.taste
> AND b.taste = 'yummy!'
> indx taste indx taste
> -- -- -- --
> 1 yummy! 1 yummy!
> 2 bummy! NULL NULL
> 3 mummy! NULL NULL
> In my opinion, there is no ambiguity at all. If you are familiar with
> the peculiarities of these two competing styles, you can get the
> results you want. Certainly the query and result themselves are not
> ambiguous to the system. Now, since it is sometimes desirable in
> practice to be able to apply filter logic to a relation *after* two
> tables have been left outer joined (in particular, in WHERE clause
> subqueries), I was curious to test the limits of MS claim. What I
> found is that you can have WHERE clause filter logic with ANSI outer
> join operators but with a twist. The trick is to add an additional
> filter which includes NULL values from your joined table. Example:
> SELECT
> *
> FROM
> #foo f
> LEFT OUTER JOIN #bar b ON b.taste = f.taste
> WHERE
> b.taste = 'yummy!' OR
> b.taste IS NULL
> indx taste indx taste
> -- -- -- --
> 1 yummy! 1 yummy!
> 2 bummy! NULL NULL
> Here we get a different result. Tuples matching my filter value
> 'yummy!' are included as well as tuples that have no matching value
> and produce NULL. The twist is rows that do have matching values in
> #bar that are not filtered for (i.e. 'mummy!') are excluded. Also,
> there is an addition drawback. If #.bar COL1 has an index defined on
> it, the system will perform an index scan as opposed to a seek.
> Nevertheless, this approach can be a significant improvement on an
> order of magnitude better if the only alternative is line - by -line
> processing.
> As of this writing, older SQL 92 syntax is unsupported in SQL Server
> 2005 level 9.0. With MS's history of dealing with competing
> technologies, I suspect there is more than meets the eye with
> Microsoft's witch hunt against *= syntax beyond the claim of ambiguity
> but I digress.|||My favorite is a table (*) with 5 rows that has NULL for a column. And a query that say col IS NULL
now suddenly returns 8 rows with NULL. Where did the 3 non-existing rows come from? Or a query
saying col IS NOT NULL returning 5 rows with NULL for that column? Or the fact that these queries
doesn't return the same result running on 2000 vs 2005 (with compatibility mode 80).
(*) The table is in fact a view, but a view should behave like a table...
Run below in 2000:
USE pubs
GO
EXEC sp_dbcmptlevel pubs, 80
GO
IF OBJECT_ID('v') IS NOT NULL
DROP VIEW v
GO
CREATE VIEW v AS
SELECT p.pub_name, t.price, t.title
FROM publishers AS p, titles AS t
WHERE p.pub_id *= t.pub_id
AND t.price IS NOT NULL -- *** OBS ***
GO
--begrunda nedanstående resultat:
SELECT *
FROM v
SELECT *
FROM v
WHERE price IS NULL
SELECT *
FROM v
WHERE price IS NOT NULL
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:46366ED0.C1FE40F3@.toomuchspamalready.nl...
> manstein,
> I must admit that I did not thoroughly read your posting.
> However, the "supposed" ambiguity of the old outer join syntax (such as
> *=) is well known and documented. Here is one posting that illustrates
> the ambiguity, and highlights this by sharing with the rest of the world
> how different RDBMS vendors (at the time) had implemented this syntax:
> differently! The posting almost dates back to the last century.
> See
> http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/518d849c9b1040c0?dmode=source
> (url may warp)
> Gert-Jan
>
>
> manstein wrote:
>> I have uncovered some interesting techniques that I wanted to share
>> with everyone. In particular, they relate to the claimed "ambiguity"
>> between using the ANSI outer join operators (LEFT OUTER JOIN, RIGHT
>> OUTER JOIN) over *= syntax by Microsoft. A little background would be
>> helpful. Microsoft has stated that it will not be supporting the
>> older *= syntax into the future. The claim is that it poses ambiguity
>> when adding filter logic to the left outer join query. If the filter
>> logic is added to the FROM clause, the LEFT OUTER JOIN is preserved.
>> In contrast, filter logic added to the WHERE clause actually turns a
>> left outer join into an inner join. Example:
>> CREATE TABLE #foo (indx INT IDENTITY(1,1), taste VARCHAR(255));
>> CREATE TABLE #bar (indx INT IDENTITY(1,1), taste VARCHAR(255));
>> INSERT INTO #foo (taste) VALUES ('yummy!');
>> INSERT INTO #foo (taste) VALUES ('bummy!');
>> INSERT INTO #foo (taste) VALUES ('mummy!');
>> INSERT INTO #bar (taste) VALUES ('yummy!');
>> INSERT INTO #bar (taste) VALUES ('tummy!');
>> INSERT INTO #bar (taste) VALUES ('mummy!');
>> --This will return all records from dbo.foo with matching records
>> from
>> --dbo.bar filtered by b.COL1 = "yummy!" non matching dbo.bar records
>> will
>> --produce NULLS.
>> SELECT
>> *
>> FROM
>> #foo f
>> LEFT OUTER JOIN #bar b ON b.taste = f.taste AND
>> b.taste = 'yummy!'
>> indx taste indx taste
>> -- -- -- --
>> 1 yummy! 1 yummy!
>> 2 bummy! NULL NULL
>> 3 mummy! NULL NULL
>> -- This will only return records from dbo.foo that have a matching
>> -- dbo.bar record where b.COL1 = "yummy!" In other words, this
>> produces
>> -- an inner join styled "filter"
>> SELECT
>> *
>> FROM
>> #foo f
>> LEFT OUTER JOIN #bar b ON b.taste = f.taste
>> WHERE
>> b.taste = 'yummy!'
>> indx taste indx taste
>> -- -- -- --
>> 1 yummy! 1 yummy!
>> This illustration highlights the fact that filter logic in the FROM
>> clause is applied before the join is made while filter logic in the
>> WHERE clause is applied after the join. This is a characteristic of
>> Microsoft's preferred syntax. With the older SQL 92 syntax, we have a
>> different flavor. The logic in the WHERE clause also preserves our
>> LEFT outer join with the addition of filters. Example:
>> SELECT
>> *
>> FROM
>> #foo f,
>> #bar b
>> WHERE
>> f.taste *= b.taste
>> AND b.taste = 'yummy!'
>> indx taste indx taste
>> -- -- -- --
>> 1 yummy! 1 yummy!
>> 2 bummy! NULL NULL
>> 3 mummy! NULL NULL
>> In my opinion, there is no ambiguity at all. If you are familiar with
>> the peculiarities of these two competing styles, you can get the
>> results you want. Certainly the query and result themselves are not
>> ambiguous to the system. Now, since it is sometimes desirable in
>> practice to be able to apply filter logic to a relation *after* two
>> tables have been left outer joined (in particular, in WHERE clause
>> subqueries), I was curious to test the limits of MS claim. What I
>> found is that you can have WHERE clause filter logic with ANSI outer
>> join operators but with a twist. The trick is to add an additional
>> filter which includes NULL values from your joined table. Example:
>> SELECT
>> *
>> FROM
>> #foo f
>> LEFT OUTER JOIN #bar b ON b.taste = f.taste
>> WHERE
>> b.taste = 'yummy!' OR
>> b.taste IS NULL
>> indx taste indx taste
>> -- -- -- --
>> 1 yummy! 1 yummy!
>> 2 bummy! NULL NULL
>> Here we get a different result. Tuples matching my filter value
>> 'yummy!' are included as well as tuples that have no matching value
>> and produce NULL. The twist is rows that do have matching values in
>> #bar that are not filtered for (i.e. 'mummy!') are excluded. Also,
>> there is an addition drawback. If #.bar COL1 has an index defined on
>> it, the system will perform an index scan as opposed to a seek.
>> Nevertheless, this approach can be a significant improvement on an
>> order of magnitude better if the only alternative is line - by -line
>> processing.
>> As of this writing, older SQL 92 syntax is unsupported in SQL Server
>> 2005 level 9.0. With MS's history of dealing with competing
>> technologies, I suspect there is more than meets the eye with
>> Microsoft's witch hunt against *= syntax beyond the claim of ambiguity
>> but I digress.|||There are other problems with the old *= syntax.
It is proprietary to Sybase/MS and therefore incompatible with other
DBMSs
It is limited to equijoins only
It can't do FULL outer joins
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On May 1, 6:21 am, David Portas
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> There are other problems with the old *= syntax.
> It is proprietary to Sybase/MS and therefore incompatible with other
> DBMSs
> It is limited to equijoins only
> It can't do FULL outer joins
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
David and Jert
Your posts mean nothing in regards to mine. I clearly state that
you will get different results between LEFT OUTER JOIN and *= My
point is that if you are familiar with the distinctions then you
should be able to use them as needed.
Tibor gets full credit but he is discussing views and it would seem
that is an implementation bug with SQL Server engine so thumbs down to
MS.|||"Gert-Jan Strik" wrote:
> *=) is well known and documented. Here is one posting that illustrates
> the ambiguity, and highlights this by sharing with the rest of the world
> how different RDBMS vendors (at the time) had implemented this syntax:
> differently! The posting almost dates back to the last century.
The argument in this post is specious. It is based entirely on the statement
that some implementations of *= are broken. That argument can be equally
applied against LEFT OUTER JOIN, obviously. The spelling mistakes and
obviously overblown statements make me dismiss it out of hand. According to
the post, *= is "simpoly dead wrong and dangerous". OK c00l d00d.
Maury
没有评论:
发表评论