Old join syntaxis its support by SQL Server 2000?

A question...
The old join syntaxis works in SQL Server 2000??...
I mean, this example works in SQL Server 2000??...
SELECT *
FROM Employee e, Departmend d
WHERE d.departmentId *= e.departmentIdI would think most of the previous SQL 6.5 etc. should be
upward compatible. I use INNER JOINS etc. and haven't
run across problems between the versions. What is the
previous version of SQL are you referring to?

BobbyJ

Originally posted by ericka
A question...

The old join syntaxis works in SQL Server 2000??...
I mean, this example works in SQL Server 2000??...

SELECT *
FROM Employee e, Departmend d
WHERE d.departmentId *= e.departmentId|||I'm using SQL Server 6.5 but I want to migrate to SQL Server 7.0 and I want to left like that my joins...; but I want to know if this joins (=*, *=) it'll in Sql Server 2000...

Originally posted by BobbyJ
I would think most of the previous SQL 6.5 etc. should be
upward compatible. I use INNER JOINS etc. and haven't
run across problems between the versions. What is the
previous version of SQL are you referring to?

BobbyJ|||Ok. Don't quote me but I think it should. Let's see what others respond
with.....|||use

select *
from employee e
left join departmet d on (d.departmentid = e.departmentid)

radzi.

"BobbyJ" wrote in message
news:2430436.1043361575@.dbforums.com...|||For 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.

All this to say that the Inner/Outer Right/Left syntax is more reliable and at somepoint the "*" syntax will no longer be supported.|||'ambiguous" cauze the user is id***ot and does not know what he is doing...

ORACLE 8 does not know RIGHT JOIN and uses only *=

did I get any "ambiguous" joins with Oracle? NO!

LEFT JOIN is more powerful than *= but that's it. It is not different or ambiguous, it can do just more.... NOT LESS nor DIFFERENT!

To your question... if your SQL works with =* then it WILL work just fine....

jiri|||RE:
Q1 [Does the old join syntax work in SQL Server 2000?]
A1 As others have already noted, for the most part, such older syntax is (still) supported.

Some additional points that maybe helpful:

i Consider carefully checking for any potentially problematic changes in behavior between versions. There are not very many, however if any do apply they may present some issues for your environment. Several relate to character handling and related function differences e.g.(empty string 6.x literals ' ' interpreted as a space, CHARINDEX and PATINDEX processing to generate null results, etc.).

ii Often it is possible to have the Enterprise Manager query building functionality, (not query analyzer), 'rewrite' older *= joins in the newer format (by pasting in the old format sql and clicking on verify syntax).

iii If troublesome version related behavior issues (mentioned in i above) are found to exist, you may wish to consider temporarily implementing a 6x db compatibility setting (the setting may be easily changed). This may allow significant use of 7.0 features / advantages while allowing 6.x issues to be addressed (and conveniently tested / verified) over a longer time frame.

For Example:

-- To view the current dbcmptlevel of Pubs
exec sp_dbcmptlevel
@.dbname = 'Pubs'
Go

-- To set to 6.5:
exec sp_dbcmptlevel
@.dbname = 'Pubs',
@.new_cmptlevel = 65
Go

-- Check the dbcmptlevel change to 6.5 settings
exec sp_dbcmptlevel
@.dbname = 'Pubs'
Go|||P.S. Was the timestamp access issue (RE: 6.5 timestamp column access in 7.0) resolved satisfactorily?