SQL Join condition.

Question

SQL Join condition.

SSCertifiable

Points: 7271

May 16, 2023 at 10:42 am

#4190666

Just wondering what does author want to do with below code snippet. Specifically the second join condition "AND 1= (CASE..."

SELECT *
FROM #TEMP INNER JOIN #USERTEMP U ON #TEMP.ID=U.ID
 INNER JOIN TBL1 D 
ON D.TYP LIKE (CASE WHEN U.TYPE IS NOT NULL AND U.TYPE<>'' THEN SUBSTRING(U.TYPE,1,1)
            WHEN U.L_TYPE IS NOT NULL THEN SUBSTRING(U.L_TYPE ,1,1) END)
AND 1 = (CASE WHEN D.CLS LIKE '%' + U.IND_TYPE +'%' AND D.CLS_OPERATOR='IN' THEN 1 
WHEN D.CLS NOT LIKE '%' + U.IND_TYPE +'%' AND D.CLS_OPERATOR='NOT IN' THEN 1 
WHEN D.CLS IS NULL AND D.CLS_OPERATOR='=' THEN 1 ELSE 0 END)

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

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

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

...
AND ( 
          (D.CLS LIKE '%'+ U.IND_TYPE '%'
   AND D.CLS_OPERATOR = 'IN'
   )
       OR (D.CLS NOT LIKE '%'+ U.IND_TYPE+ '%'
           AND D.CLS_OPERATOR = 'NOT IN'
          )
       OR (D.CLS IS NULL
           AND D.CLS_OPERATOR = '='
          )
)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

rhythmk SSCertifiable Points: 7271 More actions · Answer 2

Thanks Johan.

However just wondering why someone writes code like that "1 = " instead of simple AND / OR conditions.

Is there any benefit in writing such form?

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

You should ask the author.

There is no benefit writing it like that, could even be a performance downside due to the potential less optimal plan due to the calculation/conversion that is explicite with the query.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

rhythmk SSCertifiable Points: 7271 More actions · Answer 4

Unfortunately, the author is not available in my outlook.

But even I was thinking why to put additional code and ask SQL engine to decode the same which looks like overhead prima facie..

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂