unfamiliar use of =*

Question

unfamiliar use of =*

Sha_

Ten Centuries

Points: 1299
More actions
May 7, 2010 at 10:48 am

#222241

Hi all,
I have some code written by a developer who no longer works here, and they often use a star in their where clauses. I don't know what the star represents. Does anyone know?
AND R.Code =* W.TransactionType
(If this was written as AND R.Code = W.TransactionType , it of course makes sense to me. But I don't know what the star achieves. I can tell you it does change the results sets when it is removed).

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 1

sharon.souto (5/7/2010)
Hi all,
I have some code written by a developer who no longer works here, and they often use a star in their where clauses. I don't know what the star represents. Does anyone know?
AND R.Code =* W.TransactionType
(If this was written as AND R.Code = W.TransactionType , it of course makes sense to me. But I don't know what the star achieves. I can tell you it does change the results sets when it is removed).

That is a right outer join syntax for an ANSI-89 style join.

You may want to check out outer joins in Books Online.

Lowell SSC Guru Points: 323482 More actions · Answer 2

that's the old, deprecated syntax to designate outer joins:

--AND R.Code = W.TransactionType is the same as

FROM R INNER JOIN W ON R.CODE = W.TransactionType

--AND R.Code *= W.TransactionType is the same as

FROM R LEFT OUTER JOIN W ON R.CODE = W.TransactionType

--AND R.Code =* W.TransactionType is the same as

FROM R RIGHT OUTER JOIN W ON R.CODE = W.TransactionType

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sha_ Ten Centuries Points: 1299 More actions · Answer 3

Thank you, Lynn and Lowell. This helped me more than you know. The BOL explanation says that the older use of this join is not always accurate. It explains the data problem I was having, which I couldn't previously explain. A join rewrite to standards corrected my problem.

Thank you so much!!

Sharon

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 4

Lynn Pettis

SSC Guru

Points: 442447

May 7, 2010 at 11:50 am

#1164336

Glad we could help.