[thelist] followup SQL question

Scott Dexter sgd at ti3.com
Fri Sep 22 14:09:49 CDT 2000


> -----Original Message-----
> From: Rudy_Limeback at maritimelife.ca
> 
> >  SELECT * FROM Services, Operators
> >   WHERE Services.OperatorID .= Operators.OperatorID
> 
> is that some kind of outer join syntax?  which database?
> 
> sql/server uses an asterisk instead of a dot...
> 
>    SELECT * FROM Services, Operators
>     WHERE Services.OperatorID *= Operators.OperatorID

And to be completely up to date, SQL Server 7.0 docs state:

In earlier versions of Microsoft® SQL Server(tm), 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 will 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.
**

In other words, SQL Server is preferring the long form:

instead of
>    SELECT * FROM Services, Operators
>     WHERE Services.OperatorID *= Operators.OperatorID

you do

    SELECT * FROM Services LEFT OUTER JOIN Operators ON Services.OperatorID
= Operators.OperatorID


sgd
--
work: http://www.ti3.com/
non: http://thinksafely.org/





More information about the thelist mailing list