How to check if a particular mapping exists in a one-to-many mapping table

Question

I am having a table that maintains the mapping of an EMPLOYEE_ID to the one or more ROLE_IDs that the employee can be assigned with. The ROLE_ID is a primary key of the ROLE table.

Now, I am trying to find if a particular employee is a Team Leader (ROLE_ID = 2) or not. That is, in essence, trying to find if the particular mapping combination of (EMPLOYEE_ID, 2) exists in the mapping table.

Currently, I am using the below query to achieve this:

SELECT E.NAME AS `EMPLOYEE_NAME`, 
 EXISTS( SELECT 1 FROM `EMPLOYEE_ROLE` WHERE 
   (`EMPLOYEE_ROLE`.`EMPLOYEE_ID` = `E`.`EMPLOYEE_ID`)
   AND (`EMPLOYEE_ROLE`.`ROLE_ID` = 2)) AS `IS_TEAM_LEADER`
 -- Assume some other column shall be selected from ER table, 
 -- hence necessitating the JOIN on ER
FROM EMPLOYEE E
JOIN EMPLOYEE_ROLE ER ON (ER.EMPLOYEE_ID = E.EMPLOYEE_ID)
GROUP BY E.EMPLOYEE_ID;

Although this seems to get the job done, I am looking for a more efficient approach, as the subquery in its current form seems redundant. Not sure if it's relevant, but can FIND_IN_SET or some such function be used?

Can anyone suggest a solution, as I am interested in the best-performing approach?

EDIT 1: I have intentionally used the JOIN EMPLOYEE_ROLE with the intention that some other column also may be picked from the ER table. So, I am looking for optimising the subquery, while keeping that join intact. Hence, the statement "current subquery in its current form seems redundant".

SQLFiddle: http://sqlfiddle.com/#!9/2aad3/5

Answer 1

Either use the exists subquery or use join, but you should not use both in one query.

I would use the join approach, since it's easy to get role related data if necessary:

SELECT E.NAME AS `EMPLOYEE_NAME`, 
FROM EMPLOYEE E
INNER JOIN EMPLOYEE_ROLE ER ON (ER.EMPLOYEE_ID = E.EMPLOYEE_ID)
WHERE ER.ROLE_ID=2;

If you need a list of all employees with a field indicating if that employee is IS leader or not, then use left join instead of inner:

SELECT DISTINCT E.NAME AS `EMPLOYEE_NAME`, IF(ER.ROLE_ID IS NULL, 'NOT IS Leader','IS Leader') AS IsISLeader
FROM EMPLOYEE E
LEFT JOIN EMPLOYEE_ROLE ER ON ER.EMPLOYEE_ID = E.EMPLOYEE_ID AND ER.ROLE_ID=2;