Subquery optimisation

Question

Subquery optimisation

Wobble Chops

SSC Eights!

Points: 835
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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

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: 6809 More actions · Answer 2

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: 835 More actions · Answer 3

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: 835 More actions · Answer 4

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.