w3resource

MySQL Subquery Exercises: Find the name of the employees who are not supervisors

MySQL Subquery: Exercise-12 with Solution

Write a MySQL query to find the name (first_name, last_name) of the employees who are not supervisors.

Sample table: employees


Code:

-- Selecting the first name and last name of employees who are not managers 
SELECT b.first_name, b.last_name 
-- Selecting data from the employees table, aliasing it as 'b'
FROM employees b 
-- Filtering the result set to include only employees who are not managers
WHERE NOT EXISTS 
    -- Subquery to check if there is no employee with manager_id equal to the employee_id of each employee in the outer query
    (SELECT 'X' FROM employees a WHERE a.manager_id = b.employee_id);

Explanation :

  • This MySQL code selects the first name and last name of employees from a table named "employees".
  • It aliases the table as 'b' for easier reference in the query.
  • The query filters the results to only include employees who are not managers.
  • This is achieved using a subquery with the NOT EXISTS clause, which checks if there is no employee with a manager_id equal to the employee_id of each employee in the outer query. If such an employee doesn't exist, the condition is true, and the employee is included in the result set.

MySQL Subquery with EXISTS or NOT EXISTS

If a subquery returns any rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE. For example :

SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

Traditionally, an EXISTS subquery starts with SELECT *, but it could begin with SELECT 5 or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery, so it makes no difference.

MySQL SubQueries: Find the names of the employees who are not supervisors

 

MySQL Code Editor:

Structure of 'hr' database:

hr database

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous:Write a MySQL query to find the name (first_name, last_name) and salary of the employees who earn a salary that is higher than the salary of all the Shipping Clerk (JOB_ID = 'SH_CLERK').
Next:Write a MySQL query to display the employee ID, first name, last name, and department names of all employees.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.