difference between *= and Left outer join

Question

difference between *= and Left outer join

karthik M

One Orange Chip

Points: 29139
More actions
November 5, 2007 at 6:34 am

#183561

Hi Experts,
Kindly let me know the diffference between *= and Left Outer join.
karthik

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

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

David McFarland SSChampion Points: 11815 More actions · Answer 1

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.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

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?

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

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)

karthik M One Orange Chip Points: 29139 More actions · Answer 4

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 5

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

karthik M One Orange Chip Points: 29139 More actions · Answer 6

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

karthik

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

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)

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 8

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

Loner SSC-Insane Points: 21279 More actions · Answer 9

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.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 10

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)

Loner SSC-Insane Points: 21279 More actions · Answer 11

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:

sing4you SSCrazy Points: 2984 More actions · Answer 12

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. 🙂

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 13

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