SQL : select record from table 1 which are not in table 2

Question

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?

Answer 1

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.

Answer 2

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)