Cannot find correct number of values in a table that are not in another table, though I can do otherwise

Question

I want to retrieve the course_id in table course that is not in the table takes. Table takes only contains course_id of courses taken by students. The problem is that if I have:

select count (distinct course.course_id)
from course, takes
where course.course_id = (takes.course_id);

the result is 85 which is smaller than the total number of course_id in table course, which is 200. The result is correct.

But I want to find the number of course_id that are not in the table takes, and I have:

select count (distinct course.course_id)
from course, takes
where course.course_id != (takes.course_id);

The result is 200, which is equal the number of course_id in table course. What is wrong with my code?

Welcome to Stack Overflow! We'll be happy to help out, but do let us know if this is homework - that will point us towards whether you just need a 'this is the code you want' answer or 'this is where you're headed wrongly, let me explain what you need to be doing' answer :) — AHiggins, Oct 15 '14 at 14:50

score 0 · Answer 1 · answered Oct 15 '14 at 14:49

This SQL will give you the count of course_id in table course that aren't in the table takes:

select count (*)
from course c
where not exists (select * 
                  from takes t 
                  where c.course_id = t.course_id);

You didn't specify your DBMS, however, this SQL is pretty standard so it should work in the popular DBMSs.

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

There are a few different ways to accomplish what you're looking for. My personal favorite is the LEFT JOIN condition. Let me walk you through it:

Fact One: You want to return a list of courses

Fact Two: You want to filter that list to not include anything in the Takes table.

I'd go about this by first mentally selecting a list of courses:

SELECT c.Course_ID
FROM Course c

and then filtering out the ones I don't want. One way to do this is to use a LEFT JOIN to get all the rows from the first table, along with any that happen to match in the second table, and then filter out the rows that actually do match, like so:

SELECT c.Course_ID
FROM 
    Course c
     LEFT JOIN -- note the syntax: 'comma joins' are a bad idea.
    Takes t ON 
        c.Course_ID = t.Course_ID -- at this point, you have all courses
WHERE t.Course_ID IS NULL -- this phrase means that none of the matching records will be returned.

Another note: as mentioned above, comma joins should be avoided. Instead, use the syntax I demonstrated above (INNER JOIN or LEFT JOIN, followed by the table name and an ON condition).

Cannot find correct number of values in a table that are not in another table, though I can do otherwise

2 Answers2