Post reply

unfamiliar use of =*

  • 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).

  • May 7, 2010 at 10:52 am

    #1164279

    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.

  • May 7, 2010 at 10:52 am

    #1164280

    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!

  • May 7, 2010 at 11:39 am

    #1164327

    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

  • May 7, 2010 at 11:50 am

    #1164336

    Glad we could help.

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

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