Subquery optimisation

• Wobble Chops

  SSC Eights!

  Points: 805

  More actions

  December 7, 2011 at 3:08 am

  #251615

  Hi there,

  I'm wondering if there is a better way to write the following query (perhaps using joins instead of a subsquery). I'm using SQL 2008 and I need a list of colours that are NOT the same as the colour of the product in the WHERE clause.

  The actual tables involved have a foreign key constraint from Products(fkColourID) referencing Colours(pkID).

  -- Set up tables

  IF OBJECT_ID('tempdb..#Colours') IS NOT NULL

  DROP TABLE #Colours

  GO

  CREATE TABLE #Colours(

  pkID int PRIMARY KEY IDENTITY(1,1),

  Name varchar(20)

  )

  GO

  IF OBJECT_ID('tempdb..#Products') IS NOT NULL

  DROP TABLE #Products

  GO

  CREATE TABLE #Products(

  pkID int IDENTITY(1,1),

  Name varchar(20),

  fkColourID int

  )

  GO

  INSERT INTO #Colours

  SELECT 'Green'

  UNION ALL

  SELECT 'Orange'

  UNION ALL

  SELECT 'Yellow'

  UNION ALL

  SELECT 'Red'

  UNION ALL

  SELECT 'Blue'

  GO

  INSERT INTO #Products

  SELECT 'Apple', 4

  UNION ALL

  SELECT 'Banana', 3

  UNION ALL

  SELECT 'Pepper', 4

  UNION ALL

  SELECT 'Cucumber', 1

  UNION ALL

  SELECT 'Orange', 2

  UNION ALL

  SELECT 'Pumpkin', 2

  GO

  -- My query I'd like to optimise, if possible

  SELECT DISTINCT Name

  FROM #Colours

  WHERE pkID NOT IN (

  SELECT fkColourID

  FROM #Products

  WHERE Name = 'Banana'

  )

  GO

  -- Tidy up tables

  IF OBJECT_ID('tempdb..#Colours') IS NOT NULL

  DROP TABLE #Colours

  GO

  IF OBJECT_ID('tempdb..#Colours') IS NOT NULL

  DROP TABLE #Colours

  GO

  Many thanks in advance for any help you can give.

  Paul.

• December 7, 2011 at 3:15 am

  This was removed by the editor as SPAM

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  December 7, 2011 at 3:39 am

  #1417918

  Hi Paul

  Have a read of Gails blog post on the subject of non-existence here[/url]. Armed with your new knowledge you will choose the best option.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• davidandrews13

  SSCertifiable

  Points: 6689

  More actions

  December 7, 2011 at 4:35 am

  #1417949

  ChrisM@Work (12/7/2011)

  ---

  Hi Paul

  Have a read of Gails blog post on the subject of non-existence here[/url]. Armed with your new knowledge you will choose the best option.

  I've learnt something today. Gails article has helped me understand a problem that i had previously been having and couldn't work out why:

  The most important thing to note about NOT EXISTS and NOT IN is that, unlike EXISTS and IN, they are not equivalent in all cases. Specifically, when NULLs are involved they will return different results. To be totally specific, when the subquery returns even one null, NOT IN will not match any rows.

  it confused me why i was getting no rows back sometimes when using a NOT IN. makes sense now 🙂

• Wobble Chops

  SSC Eights!

  Points: 805

  More actions

  December 15, 2011 at 8:21 am

  #1421985

  That's exactly what I was looking for. Thank you!

  The part I was missing was the ability to have an AND clause as part of the JOIN. I have to admit that I didn't even know you could do that so I've learnt something new! 🙂

  Thanks again,

  Paul.

• Wobble Chops

  SSC Eights!

  Points: 805

  More actions

  December 15, 2011 at 8:32 am

  #1421996

  Hi Chris,

  The fundamental fact I was missing (ability to have 'AND column = value' in joins) wasn't in that post but it was highly informative, all the same.

  Thanks for your response,

  Paul.