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

Answer 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.

Answer 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