Question about subquery in SELECT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Question about subquery in SELECT

Author

Topic

AdamKosecki
Starting Member

18 Posts

Posted - 2006-09-10 : 23:53:46

If I do this:

SELECT name, description,
(
SELECT COUNT(1)
FROM images
WHERE setid = image_sets.id
) AS image_count
FROM image_sets
WHERE id = $VARIABLE

Does it execute the subquery with the count on every row in the database before comparing the WHERE statement? In which case, it would be more efficient to do this:

SELECT name, description,
(
SELECT COUNT(1)
FROM images
WHERE setid = sets.id
) AS image_count
FROM (
SELECT id, name, description,
FROM image_sets
WHERE id = $VARIABLE
)AS sets

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 00:55:30

Try this


SELECT		$VARIABLE ID,
		is.Name,
		is.Description,
		SUM(CASE WHEN i.setid is null then 0 else 1 end) image_count
FROM		image_sets is
LEFT JOIN	images i ON i.setid = is.id
WHERE		is.id = $VARIABLE
GROUP BY	is.Name,
		is.Description

Peter Larsson
Helsingborg, Sweden

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-09-11 : 19:36:38

Try looking at the query plans for each variation - that will tell you.

AdamKosecki
Starting Member

18 Posts

Posted - 2006-09-11 : 20:48:47

Oh cool! Thanks Peso.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources