Help with simple SQL Server query

Question

I have to tables as follows;

Employees: Name nvarchar(50), Job Title nvarchar(50) and Salary int.
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.

itchi · Accepted Answer · 2010-05-30 02:15:20Z

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

edited May 30, 2010 at 2:15

answered May 29, 2010 at 22:53

itchi

1,71314 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andomar Over a year ago

If any employee has a null job title, this query will return zero rows. x not in (null, 'a', ... -> x <> null and x <> 'a' and .. evaluates to unknown

Blorgbeard Over a year ago

Wow, just tested that cos I'd never encountered it and it's true. Fixed by adding where jobtitle is not null to the subselect, though.

Andomar · Accepted Answer · 2010-05-29 22:55:26Z

0

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 :)

answered May 29, 2010 at 22:55

Andomar

239k55 gold badges387 silver badges412 bronze badges

2 Comments

Chris Bednarski Over a year ago

this will not return the desired output unless there is a employers.jobtitle = null. forgot a left join somewhere?

erikkallen Over a year ago

Don't do this on SQL server, it's not smart enough to understand it is an anti-join.

Collectives™ on Stack Overflow

Help with simple SQL Server query

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related