How to display rows which don't match a row in another table?

Question

I want to display staff that are not ward managers.

I have this query which displays ward managers:

SELECT Name, Grade, WardManager, StaffNo  
FROM STAFF, WARD   
WHERE WARD.WardManager = STAFF.StaffNo;

However I can't figure out how to display the staff who aren't ward managers. I have tried a combination of IS NOT, NOT LIKE, NOT IN etc and none of them seem to work.

I don't see any reason for this to not be possible if the data types are same. What error are you getting? — Kamal, Aug 09 '12 at 11:43

score 1 · Answer 1 · answered Aug 09 '12 at 11:39

1

Not sure I understand you table structure correctly, but when someone complains that NOT IN returns no rows, it's almost always a problem with NULL values in the column used for the IN operator.

Try this one:

select *
from staff
where staffno not in (select wardmanager 
                      from ward 
                      where wardmanager IS NOT NULL)

answered Aug 09 '12 at 11:39

@henry: see here for a more detailed explanation: http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx (it's SQL Server syntax but the basic logic is the same) – Aug 09 '12 at 11:46

score 0 · Answer 2 · edited May 23 '17 at 10:34

To give an alternative to a_horse_with_no_name's answer this can also be done with a left outer join:

select s.*
  from staff s
  left outer join ward w
    on s.staffno = w.wardmanager
 where w.wardmanager is null

This works be joining on what you want but then asking for values that do not match.

As to which is quicker / more efficient it depends on your specific circumstances so you would have to test both and see.

As there is no, relevant, data in the ward table ( all columns will be null ) there is no point selecting columns from this table. However, the benefit of a join is that you can, if you want. Say, for instance, you removed the where clause you could return staff that are ward manager's and staff that are not. The differentiation being that columns from ward would always be null when they are not a manager.

Yet another method of doing this is using where exists, but like not in this will only be suitable if you do not want to return any columns from ward.

select s.*
  from staff s
 where not exists ( select 1
                      from ward w
                     where wardmanager = s.staffno )

Once again speed and efficiency is determined by your circumstances and how many rows there are in the tables.

How to display rows which don't match a row in another table?

2 Answers2

Linked