0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
kelvinfix
  • 2,915
  • 8
  • 37
  • 49
  • 1
    Joins are typically faster than where limits. – xQbert May 08 '12 at 03:43
  • Does the sales table have any indexes on it? – ssis_ssiSucks May 08 '12 at 03:52
  • When was the last time that stats were updated? Has it been a while? Have a significant # of rows been added since? – brian May 08 '12 at 03:57
  • No index, only salesno is the primary key. – kelvinfix May 08 '12 at 04:01
  • xQbert, Is this fact or your opinion? There will be a join regardless. The join algorithm chosen by the optimizer is what is in question. Many would say a NOT EXISTS or, if there will not be NULLS, a NOT IN would be the best option. Typically, a LEFT JOIN will be a complete join, returning all matching rows (including duplicates) and then filtering. A NOT EXISTS or NOT IN will more likely be an anti-semi join (partial join), not joining all rows but simply checking for absence of existence. http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/ – brian May 08 '12 at 04:18

1 Answers1

4

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
Ritesh D
  • 228
  • 1
  • 6