Re: Difference between * = and LEFT Outer Join

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 05/31/04 

Date: Mon, 31 May 2004 09:06:47 -0400

I don't have an example but this is from BOL:

In earlier versions of Microsoft® SQL ServerT 2000, left and right outer
join conditions were specified in the WHERE clause using the *= and =*
operators. In some cases, this syntax results in an ambiguous query that can
be interpreted in more than one way. SQL-92 compliant outer joins are
specified in the FROM clause and do not result in this ambiguity. Because
the SQL-92 syntax is more precise, detailed information about using the old
Transact-SQL outer join syntax in the WHERE clause is not included with this
release. The syntax may not be supported in a future version of SQL Server.
Any statements using the Transact-SQL outer joins should be changed to use
the SQL-92 syntax.

The SQL-92 standard does support the specification of inner joins in either
the FROM or WHERE clause. Inner joins specified in the WHERE clause do not
have the same problems with ambiguity as the Transact-SQL outer join syntax. 

-- 
Andrew J. Kelly
SQL Server MVP
"Hari" <hari_prasad_k@hotmail.com> wrote in message
news:%237cnu9vREHA.2404@TK2MSFTNGP09.phx.gbl...
> Hi Guys,
>
> Is there any difference in using * = and LEFT OUTER Join apart from ANSII
> complient.
> Reson is today I have received 2 different results set in the usage of
both.
> Some time back I have read in one article stating that there is some
> difference in both usage, but I couldnt recollect.
> Can you please tell me if there is any difference which will give you
> diffrent result set for some queries.
>
> Thanks
> Hari
>
>
>

Relevant Pages

  • Re: CROSS JOIN
    ... > was that depreciating the original FROM .. ... WHERE syntax would never ... > The problem is that the WHERE clause is done after the FROM clause. ... but thats just outer join stuff. ...
    (comp.databases)
  • Re: Better "Join" vs "Where" clause?
    ... running the SQL directly, in the SQL Server tools, rather than ... WHERE clause has been deprecated, ... AFAIK one cannot perform an outer join in Access without using the explicit ...
    (microsoft.public.access.queries)
  • Re: Performance: Conditions in WHERE clause vs. conditions in INNER JOIN?
    ... I hated the "new" syntax for a long time because I was used to the ... find I like it quite a bit with join conditions in the ON clause, ... is null" when doing an outer join with the ... join conditions in the WHERE clause. ...
    (comp.databases.ms-sqlserver)
  • Re: Syntax Help!
    ... If the Access people does not know their syntax, ... LEFT OUTER JOIN ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Full Outer Join?
    ... I do not think there is *old* style syntax for the FULL OUTER JOIN. ... this syntax will be dropped in a SQL Server 2005, so there is no reason to ... moving database-specific code inside of the SP ...
    (microsoft.public.dotnet.framework.adonet)