Convert Sql Server Query from In -> Exists?

Question

I want to convert this query :

SELECT *
FROM   myTable
WHERE   dateCreated = (
                       SELECT MAX(dateCreated)
                       FROM   myTable
                      )
      AND duty IS NULL
      AND CompanyPayingId   IN 
                                  (43081 ,43082 ,43084 ,43085)

to change from in to Exists query ( due to performance) :

However I'm having trouble converting the code cause I think I'll have to duplicate the where clause...

How can I convert this code ?

Why do you think that converting from `IN` to `EXISTS` will improve performance? — Martin Smith, Aug 06 '12 at 08:36
@MartinSmith http://stackoverflow.com/questions/2065329/sql-server-in-vs-exists-performance — Royi Namir, Aug 06 '12 at 08:37
The accepted answer there is wrong IMO. As far as I've seen `IN` and `EXISTS` always get the same plan. See Aaron's comment on it. — Martin Smith, Aug 06 '12 at 08:38

score 1 · Accepted Answer · answered Aug 06 '12 at 08:28

I can do like this:

create table num(idn int)
insert into num values(43081) ,(43082),(43084) ,(43085)
select * from num
SELECT *
FROM   myTable m inner join num n
ON  m.dateCreated = (SELECT MAX(dateCreated) FROM   myTable)
      AND m.duty IS NULL
      AND m.CompanyPayingId=n.num

Convert Sql Server Query from In -> Exists?

1 Answers1