Hi,

I like to know how do we optimize SQL code
using IN into separate query from

USE pubs
SELECT pub_name
FROM publishers
WHERE pub_id IN <============
(SELECT pub_id
FROM titles
WHERE type = 'business')

Is there any better way to query the subquery first,
then place the result inside the IN (...)

Like

USE pubs
SELECT @result = pub_id
FROM titles
WHERE type = 'business'


SELECT pub_name
FROM publishers
WHERE pub_id IN (@result)


Since most of my SQL stored procedure is slowing down
due to subquery. I am trying to optimize it
and not sure why some query is working, some is halting
my DB (lock). It does not have this problem when my
DB size is less than 1GB.

Any advise or comment is very much appreciated.

Thank you