SQL Server query : SELECT 1 WHERE EXISTS versus SELECT TOP 1 1

Question

I need to present a flag - 0 if a condition is not meet, 1 if it is - and I can do it in two different ways :

Get Employee ID, name, 1 if has others in suborder - 0 if not :

SELECT e.ID
     , e.Name
     , ISNULL ( ( SELECT TOP 1 1 FROM Employee se WHERE se.ManagerID = e.ID ) , 0 ) AS HasSubordinates 
  FROM Employee e

or

SELECT e.ID
     , e.Name
     , ISNULL ( ( SELECT 1 WHERE EXISTS ( SELECT * FROM Employee se WHERE se.ManagerID = e.ID ) ) , 0 ) AS HasSubordinates 
  FROM Employee e

Which version would you choose and why ?

Update 1

How about this one ?

SELECT o.ID
     , o.Name
     , o.StartDate
     , o.EndDate
     , ISNULL ( ( SELECT TOP 1 1 FROM changes c WHERE c.ChangeDate BETWEEN o.StartDate AND o.EndDate ) , 0 ) AS IsChanged
  FROM Orders o

or

SELECT o.ID
     , o.Name
     , o.StartDate
     , o.EndDate
     , ISNULL ( ( SELECT 1 WHERE EXISTS ( SELECT * FROM changes c WHERE c.ChangeDate BETWEEN o.StartDate AND o.EndDate ) ), 0 ) AS IsChanged
  FROM Orders o

Solution 1

Neither, I'd use:

   SELECT t.id,
          t.name,
          CASE WHEN x.managerid IS NULL THEN 0 ELSE 1 END AS HasSubordinates 
     FROM EMPLOYEE t
LEFT JOIN (SELECT DISTINCT 
                  e.managerid
             FROM EMPLOYEE e) x ON x.managerid = t.id

...because correlated SELECTS in the SELECT clause are bad - they do not scale, because they execute for every row returned. Which means the more rows you have, the more times the correlated SELECT will be called.

Solution 2

I wouldn't do either...

The reason being that (and this is as far as I know) when you have a subselect in the select statement, that subselect will be executed once for every row that is returned. Hence if you had 100 rows returned by the main query you would in effect be running 101 queries. When you use a join you are only doing one query that needs to match the left and the right together. Note it would help if you have an index on ManagerId.

Try something like this:

SELECT e.ID,
       e.Name,
       COUNT(se.ID) AS TotalStaff
FROM    Employee e 
        LEFT JOIN Employee se 
                ON se.ManagerID = e.ID
GROUP BY e.ID, e.Name 

This is slightly different to what you had as I am returning the total and not just a 0|1 but that is easy to change if you need the 1|0...

Let me know if this helps

Comments

[MiniMe]

Thanks OMG Ponies for the quick answer. I will validate the right one. BTW: How much time are you spending on stackoverflow - I see you are pretty active :)

[OMG Ponies]

The Update would return the same value for all rows. And it's disrespectful to those who've already provided answers by changing the criteria.

[mjv]

' beat me to it! That definitively the way to go! +1!

[MiniMe]

I was looking at another OMG Ponies 's question ( stackoverflow.com/questions/1590208 ) right after posting this one - why DISTINCT and not GROUP BY ?

[OMG Ponies]

@MiniMe: Because it doesn't use aggregate functions. GROUP BY e.managerid is the equivalent, and will perform the same.

[MiniMe]

COUNT(se.ID) will go through all rows in the LEFT JOIN table. I'm trying to avoid this by just selecting the first one that matches my criteria - or using EXISTS.