ANSI vs SQL 92 ambiguity ?!? yeah right

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 nedanstende 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...[vbcol=seagreen]
> 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:
|||"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