SQL Server speed up query with not in

Question

I have a table called sales with the following few columns:

salesno (PK, char(25))
advanceno (char(15), not null)

Now I want to select all the rows where salesno is not in advanceno:

SELECT salesno 
FROM sales 
WHERE salesno NOT IN (SELECT advanceno FROM sales)

The query is slow because the sales table has hundred thousands of rows.

I did this, and it is very fast:

SELECT salesno 
FROM sales 
WHERE salesno NOT IN ('000008360', '000008361', '000008362', '000008363', '000008364')

How can I optimise the query?

Answer 1

Try this:

DECLARE @sales as Table (salesno char(25)
,advanceno char(15) not null)

INSERT INTO @sales(salesno,advanceno)
SELECT '000008360','000008360' UNION ALL
SELECT '000008361','000008362' UNION ALL
SELECT '000008362','000008364' 

SELECT  s.salesno 
FROM    @sales s 
LEFT JOIN @sales a ON a.advanceno = s.salesno
WHERE   a.advanceno IS NULL

In your example, try this

SELECT  s.salesno 
FROM    sales s 
LEFT JOIN sales a ON a.advanceno = s.salesno
WHERE   a.advanceno IS NULL