Reputation: 319
I have two database tables: "employee" and "department". employee's table has two columns Primary_key id and emp_name and in department table emp_id, and dep_name.
There's supposed to be a relation b/w table as a foreign key, but for some reason this relationship is virtual
Data in tables like
employee
id emp_name
1 'abc'
2 'efg'
4 'hij'
department
emp_id dept_name
1 'it'
2 'engineering'
3 'management'
5 'process'
want to select all records from the department table which are not in the employee table.
one solution is
select d.*
from department
where d.id not in(select id from employee);
is there any better optimized way?
Upvotes: 4
Views: 6809
Reputation: 312136
Using in
, as you did, is fine. Using exists
, however, may perform a tad faster:
SELECT *
FROM department d
WHERE NOT EXISTS (SELECT *
FROM employee e
WHERE d.emp_id = e.id)
Upvotes: 2
Reputation: 176124
You can use LEFT JOIN
:
SELECT d.*
FROM department d
LEFT JOIN employee e
ON d.emp_id = e.id
WHERE e.id IS NULL;
You should compare execution plans to check which query has the best performance.
Upvotes: 5