We all are familiar with selecting the rows which has common data in multiple tables. And yes using joins are the most popular solution among the developers.
Now question is how to select rows which doesn’t exists in other tables.
lets say there are two tables employee and department.
GOAL : Now, I want to find out the employees who doesn’t belong to any department.
We have some ways to achieve this goal.
- using NOT EXISTS
SELECT * FROM employee WHERE NOT EXISTS (SELECT 1 FROM department WHERE employee.id = department.empId)
This will return all the employees which
NOT EXISTS in
SELECT * FROM employee LEFT JOIN department ON employee.id = department.empId WHERE department.empId IS NULL
3. EXCEPT in PLSQL (doesn’t work with mySql)
SELECT * FROM employee EXCEPT ALL SELECT empId FROM department
4. NOT IN
SELECT * FROM employee WHERE id NOT IN ( SELECT empId FROM department )