...
...
 WHERE Employee = 'Rob'
   OR  NOT EXISTS
       (SELECT 0
         FROM  table_a
         WHERE Employee = 'Rob'
       )
   AND Division = 'SuperDivision'
   OR  NOT EXISTS
       (SELECT 0
         FROM  table_a
         WHERE Employee = 'Rob'
           OR  Division = 'SuperDivision'
       )
   AND Company = 'MegaCorp'
;

SELECT Company , Division , Employee
 FROM (SELECT t.*
            , RANK()
                 OVER( ORDER BY
                             CASE
                             WHEN Employee = 'Rob'           THEN 0
                             WHEN Division = 'SuperDivision' THEN 1
                             WHEN Company  = 'MegaCorp'      THEN 2
                             ELSE                                 9
                             END
                     ) rank
        FROM  table_a AS t
      )
 WHERE rank = 1
   AND
  (    Company  = 'MegaCorp'
   OR  Division = 'SuperDivision'
   OR  Employee = 'Rob'
  )
;