How to select rows which doesn’t exist in other table in SQL

Standard

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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s