Legacy syntax SQL Help

[fredp1]

Hi,
SQL2k

For Testing purposes only, i'm trying to write some sql using the Legacy Syntax, not the SQL-92 syntax.

Use NORTHWIND
SELECT customers.contactname, orders.freight, employees.Firstname
from customers, orders, employees
where customers.CustomerID*=orders.CustomerID and Orders.employeeid*=employees.employeeid

When I run it Query Analyser, i get the following error...
Query contains an outer-join request that is not permitted.

How can I get the server to process the query?

 

[amorous]

Try this:

Use NORTHWIND
SELECT customers.contactname, orders.freight, employees.Firstname
from customers
INNER JOIN orders 
ON customers.CustomerID=orders.CustomerID
INNER JOIN employees
ON Orders.employeeid=employees.employeeid

-VJ

 

[fredp1]

Hi VJ,

I don't want to use the SQL-92 syntax(which you kindly provided), just the legacy syntax. I am doing some testing to understand the behavior difference.

Thanks

 

[SQLSister]

I don't know why your code wouldn't run, maybe because you had two outer joins and that confused it? I will tell you however that the legacy code is not a good idea in SQL server as it is not interpreted correctly all the time. Sometimes SQL Server will interpret that as a cross-join instead of an outer join.

From Books Online:

Transact-SQL Joins
In earlier versions of Microsoft® SQL Server™ 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.

 

[fredp1]

Wierd... I just rearranged the tables in the where clause and the query works.
I thought the SQL2k would now accept the code at all.

SELECT customers.contactname, orders.freight, employees.Firstname
from customers, orders, employees
where customers.CustomerID*=orders.CustomerID and employees.employeeid*=Orders.employeeid

I can now play with the code to understand how legacy and and sql-92 syntax behave differentlty.

Thanks for everyones help.
Fred