Change Sql "Not In" To "Left Outer Join"

Question

Hi,

I don't want to use "not in" this sql query. How can I do it? thanks

SELECT  
 T2.Sno,
 T2.Name,
 T1.description,
 T2.UserCode            
FROM 
 Table1 AS T1 (nolock)
    INNER JOIN T2 (nolock)
    ON T1.UserCode = T2.UserCode
WHERE
 g.xid= @p_xid
 and T2.Sno not in (select Gid from T3 (nolock))

Answer 1

Assuming there is no row in T2 where Sno is null and in T3 where Gid is null:

SELECT  
 T2.Sno,
 T2.Name,
 T1.description,
 T2.UserCode            
FROM 
 Table1 AS T1 WITH (nolock)
    INNER JOIN T2 WITH (nolock)
      LEFT JOIN T3 WITH (NOLOCK)
      ON T2.Sno = T3.Gid
    ON T1.UserCode = T2.UserCode
WHERE
 g.xid= @p_xid
 and T3.Gid IS NULL

Answer 2

If you have multiple T3 rows per T2.Sno = T3.Gid, you'll need DISTINCT in a JOIN.

• Without DISTINCT, it's a different query
• With DISTINCT, it's an extra step.

I'd use NOT EXISTS which avoids this.

SELECT  
 T2.Sno,
 T2.Name,
 T1.description,
 T2.UserCode            

FROM 
 Table1 AS T1 (nolock)
    INNER JOIN T2 (nolock)
    ON T1.UserCode = T2.UserCode
WHERE
 g.xid= @p_xid
 and not exists (select * from T3 (nolock) where T3.Gid = T2.Sno)