which select to use

Question

which select to use

arkiboys 9,711

can you let me know which of the below sql is best to use and why? As you see I can either use lines 2 and 3 or just use line 5 instead. So is it best to use lines 2 and 3 or just line 5? thanks User's image

Olaf Helper 47,621 Reputation points

2024-02-07T07:29:07.78+00:00

Your post is difficult to read, please fix it. And it's very unclear, please post some sample data and what you are trying to achieve here.
arkiboys 9,711 Reputation points

2024-02-07T08:09:40.8166667+00:00

the site does not seem to allow me to format the text correctly. Is it better formatted now? basically would like to know if line --5 is better to be used instead of both lines --2 and --3 . thank you
Olaf Helper 47,621 Reputation points

2024-02-07T08:18:20.8533333+00:00

Post is readable now, but still unclear without samples/requirements
LiHongMSFT-4306 31,621 Reputation points

2024-02-29T05:41:44.0433333+00:00

Hi @arkiboys Do you have further question on this issue, could we offer more support?

If any answer helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

Answer accepted by question author

LiHongMSFT-4306 31,621

Hi @arkiboys

which select to use

We do not know your tables or what result you want.

If both of them return same result, then why not test by yourself?

I could only give you some theoretical suggestions, like:

Avoid too many OR conditions. Try to rewrite where clause by removing unnecessary conditions or changing to use a function such as case or decode.
Find out any possible to change from hash join to EXISTS.
Check the execution plan and see if any chance to improve.

Best regards,

Cosmog Hong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

0 comments

1 additional answer

Your answer

Olaf Helper 47,621 Reputation points

2024-02-07T07:29:07.78+00:00

Your post is difficult to read, please fix it. And it's very unclear, please post some sample data and what you are trying to achieve here.
arkiboys 9,711 Reputation points

2024-02-07T08:09:40.8166667+00:00

the site does not seem to allow me to format the text correctly. Is it better formatted now? basically would like to know if line --5 is better to be used instead of both lines --2 and --3 . thank you
Olaf Helper 47,621 Reputation points

2024-02-07T08:18:20.8533333+00:00

Post is readable now, but still unclear without samples/requirements
LiHongMSFT-4306 31,621 Reputation points

2024-02-29T05:41:44.0433333+00:00

Hi @arkiboys Do you have further question on this issue, could we offer more support?

If any answer helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

Answer 1

arkiboys 9,711

I am not using any specific tables for my queries above. Over time I have seen some developers use one method and others use the other method. Therefor I wonder what the difference of the two is as they both look like giving doing the same thing and I would like to learn about their comparison as to which is best to use. Thank you

Viorel 127K Reputation points

2024-02-08T10:31:47.5966667+00:00

I think that 5 will not work if an empty non-null @myValue should return all rows. 3 will work, but it looks too enigmatic.

If empty and null @myValue are acceptable, then try this too: where nullif(@myValue, '') is null or pt.myColumn in (select trim(value) from string_split(@myValue, ',')).
arkiboys 9,711 Reputation points

2024-02-14T07:24:44.8333333+00:00

thank you

Share via

which select to use

1 additional answer

Your answer