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

I need to specify the flag - 0 if the condition does not match, 1 if it is - and I can do this in two different ways:

Get the employee ID, name, 1, if there are others in the 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?

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
+3
source share
2 answers

I did not use either:

   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

... SELECTS SELECT - , . , , SELECT.

+8

...

, ( , ), select, . , 100 , , 101 . , , . , , ManagerId.

- :

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 

, , , 0 | 1, , 1 | 0...

,

+2

Source: https://habr.com/ru/post/1729022/


All Articles