Help with simple SQL Server query

Question

I have to tables as follows;

1. Employees: Name nvarchar(50), Job Title nvarchar(50) and Salary int.
2. Employers: Name nvarchar(50), Job Title nvarchar(50)

I would like to select every item from the 'Employers' table where 'Job Title' does NOT show up in the 'Employees' table.

I know this is a simple query but it has me stumped. I'd be grateful for any help. Thanks.

Answer 1

select * from employers 
where jobtitle not in (select jobtitle 
     from employees
     where jobtitle is not null);

I would consider having a jobs table with foreign keys to both employees and employers

edit - thanks all for the not null fix

Answer 2

You could use a join:

select * 
from employers 
left join (
    select distinct jobtile
    from employees
) emp on employers.jobtitle = emp.jobtitle
where emp.jobtitle is null

Itchi's approach is more readable, but don't forget an where jobtitle is not null at the end of the subquery :)