Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2003
    Posts
    14

    Post Unanswered: Old join syntaxis it's 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.departmentId

  2. #2
    Join Date
    Jan 2003
    Location
    Washington, DC
    Posts
    24

    Question Re: Old join syntaxis it's support by SQL Server 2000?

    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

    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

  3. #3
    Join Date
    Jan 2003
    Posts
    14

    Re: Old join syntaxis it's support by SQL Server 2000?

    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

  4. #4
    Join Date
    Jan 2003
    Location
    Washington, DC
    Posts
    24

    Wink

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

  5. #5
    Radzi Guest

    Re: Old join syntaxis it's support by SQL Server 2000?

    use

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


    radzi.


    "BobbyJ" wrote in message
    news:2430436.1043361575@dbforums.com...

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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.
    Paul Young
    (Knowledge is power! Get some!)

  7. #7
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    '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

  8. #8
    Join Date
    Oct 2002
    Posts
    369

    Re: Old join syntaxis it's support by SQL Server 2000?

    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

  9. #9
    Join Date
    Oct 2002
    Posts
    369

    Question Re: Old join syntaxis it's support by SQL Server 2000?

    P.S. Was the timestamp access issue (RE: 6.5 timestamp column access in 7.0) resolved satisfactorily?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •