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.

Employee:

id	name	address
1	Shubham	India
2	Mack	USA

Department:

id	empId	name
1	1	Development

GOAL : Now, I want to find out the employees who doesn’t belong to any department.

We have some ways to achieve this goal.

1. 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 department table.

2. using LEFT JOIN / IS NULL

 

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
)

Cheers!

Happy coding!