SQL query to get records

Question

I don't know how to frame this question - so putting the sql statement directly here.

declare @tbl table(Id varchar(4),Userid varchar(10),Name varchar(max),Course varchar(max))
insert into @tbl values
('1','UserID1','UserA','Physics'),
('2','UserID2','UserB','Chemistry'),
('3,','UserID1','UserA','Chemistry')

Now,

To get a list of users who have taken Chemistry, I would write

select * from @tbl where Course='Chemistry'

Similarly for Physics, I would write

select * from @tbl where Course='Physics'

The problem is when I try to write the query "get a list of students who haven't taken Physics". Without thinking much, I wrote this

select * from @tbl where Course!='Physics'

and it results in, which is wrong (it is getting details about UserA - though he has registered in Physics)

Id  Userid  Name    Course
2   UserID2 UserB   Chemistry
3,  UserID1 UserA   Chemistry

To fix this, I rewrote the query like this - but somehow I think this is not the right way.

select * from @tbl where Course!='Physics'
and Userid not in (select Userid from @tbl where Course='Physics')

Please help!

that's not a bad way to write that... you don't need the `Course!='Physics'` part but the `not in` part is a valid way of getting the info you want — gloomy.penguin, Oct 17 '13 at 22:15

score 1 · Answer 1 · edited May 23 '17 at 12:05

Try the following:

SELECT *
       FROM @tlb U
       WHERE NOT EXISTS (SELECT  *
                                 FROM @tbl Inner
                                 WHERE Inner.UserId = U.UserId
                                 AND Course = 'Physics')

For a full discussion of NOT IN versus EXISTS, see this question. The consensus seems to be that NOT EXISTS is preferable.

(I note that your table definition does not mark the columns as NOT NULL; if it would be appropriate to add that in your scenario, it would be a good idea.)

score 1 · Answer 2 · answered Oct 17 '13 at 22:47

If you want the list of students who haven't taken physics, then I would suggest aggregation with the having clause:

select userId
from @tbl
group by userId
having sum(case when course = 'Physics' then 1 else 0 end) = 0;

This has the obvious advantage of only returning the student ids, and not multiple rows for a student (when there are multiple other courses). It is also an example of a "set-within-sets" subquery, and is more easily generalized than the where version. On the downside, the use of not exists might be able to better take advantage of indexes.

SQL query to get records

2 Answers2