Post reply

difference between *= and Left outer join

  • November 5, 2007 at 6:34 am

    #183561

    Hi Experts,

    Kindly let me know the diffference between *= and Left Outer join.

    karthik

  • November 5, 2007 at 3:34 pm

    #748443

    While they appear to do the same thing on the surface, they are two completely different beasts, and it could take pages to outline every single nuance. They don't even always return the same result sets when run against the same data.

    What specifically are you looking for? If you're wanting to know which to use, I'd HIGHLY recommend the ANSI version.

  • November 5, 2007 at 3:39 pm

    #748446

    The *= is a leftover from a long-gone ANSI standard, which is no longer current (ANSI-89). As of SQL 7.0 that syntax was declared deprecated, but wasn't removed from the language until SQL 2005.

    2005 will not allow you to use it any more, so it has moved from deprecated to defunct.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • November 5, 2007 at 10:29 pm

    #748506

    Just in case all the soft answers haven't made an impression...

    If you use *=, you will suffer death by SQL 😉 Don't use it.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 6, 2007 at 2:55 am

    #748566

    How your peoples are saying it will give different result ?

    For example

    Table A Table B

    1 1

    2 2

    3 3

    4 4

    5 6

    *= -- > what is the output ?

    Left Outer Join --> what is the output ?

    I have tried out in my PC, Both gave me the same result.

    karthik

  • November 6, 2007 at 5:00 am

    #748592

    I blogged on this last Thursday;

    Why SQL Server 2005 Doesn't Permit Non-ANSI Style OUTER JOINs

    When you add a second condition to the WHERE clause, you have an evaluation order issue.

    K. Brian Kelley
    @kbriankelley

  • November 7, 2007 at 12:56 am

    #748993

    I am using sql2000. Both gave me the same output.

    karthik

  • November 7, 2007 at 6:32 am

    #749071

    karthikeyan (11/7/2007)

    I am using sql2000. Both gave me the same output.

    Yes... for simple queries they will work the same... for not so simple queries with many criteria, *= will surprise you with wrong answers. I don't have a current example of how badly it can screw you because I stopped using it the first time it "surprised" me way back when SQL Server 7 first came out.

    But don't take my word for it... read Books Online which states...

    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.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 7, 2007 at 7:08 am

    #749087

    karthikeyan (11/7/2007)

    I am using sql2000. Both gave me the same output.

    If you read through my blog post you'll see that when you add a second condition to the WHERE clause (other than the OUTER JOIN), you have an evaluation order issue.

    K. Brian Kelley
    @kbriankelley

  • November 7, 2007 at 7:15 am

    #749091

    Oracle used *= but now it always uses outer join. I like to use ANSI standard better, it makes more clear. The Oracle developers still use *= but one time I showed one of them using outer join, she actually changed to use outer join.

  • November 7, 2007 at 7:57 am

    #749107

    Heh... and the Oracle notation is backwards from the T-SQL notation... in Oracle (+)= means "Right Outer Join"... in SQL Server, *= means "Left Outer Join". If you believe in the myth of code portability, that would certainly make you take another look 😛

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 7, 2007 at 2:23 pm

    #749221

    That certainly gives the developer who uses both Oracle and SQL Server a headache. I have to use both Oracle and SQL Server in most of my jobs, talking about syntax, it drives me crazy. As I said before ORACLE SQL <> SQL Server SQL :crazy:

  • November 8, 2007 at 8:08 am

    #749490

    I used to do outer joins like *= and was resistant to using ANSI syntax even though my DBA strongly recommended it.

    Then one day I had a query that would not work. My DBA helped me convert it to ANSI and then it worked. I've been a believer ever since. 🙂

  • November 8, 2007 at 12:05 pm

    #749607

    I was lucky in that regard in that I was started on ANSI JOIN syntax. I have come to appreciate the change required because a lot of the ways we did security in SQL Server 2000 (sp_grantlogin, sp_addlogin, sp_droplogin, sp_revokelogin, sp_grantdbaccess, sp_helprotect) either are deprecated in SQL Server 2005 or don't work exactly right (like sp_helprotect if you have schema permissions... they don't get reported back).

    So I understand why folks who have learned *= don't want to change. Unfortunately, the ambiguity of the query when you have another condition eventually forces this.

    K. Brian Kelley
    @kbriankelley

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply