What is a SQL statement that can find all classes with no student registering in them yet?

Question

Say, in a typical university's Database System, what might be a SQL statement that can find all classes with no student registering in them yet? (when registration process is for 10 days and it is now the 3rd day).

What might be other SQL statement solutions?

Details:

Assuming the university have many Departments, and each Departments with various classes, such as Psychology 1A, taught by several different teachers at different time, and Psychology 10, Math 110, etc.

Answer 1

Here is one option using FULL OUTER JOIN:

SELECT * 
FROM Class
  FULL OUTER JOIN StudentClass
   ON Class.Id = StudentClass.ClassId
WHERE StudentClass.ClassId IS NULL

You can also use a subquery with NOT IN:

SELECT * 
FROM Class
WHERE Id NOT IN
(  SELECT ClassId
   FROM StudentClass
)

Answer 2

You can use a left join, which sticks in NULLs when there's nothing to join

SELECT class
FROM classes c
    LEFT JOIN students s ON c.class = s.class
WHERE s.name IS NULL

Answer 3

There are for basic techniques for identifying records in table A whic aren't in table B. Each has its virtues, and each may perform well or badly, depending on data distribution and volume. Also the presence of nulls in the filtering columns can lead to different results (although as it seems unlikely this is a consideration in the specific scenario described in the question). Finally, not every flavour of SQL will support all of these syntaxes.

The most straigthforward is probably the NOT IN:

select *  
from   classes 
where id not in 
        (  select class_id 
           from registrations 
        ) 
/

Next is the related NOT EXISTS:

select *  
from   classes 
where not exists 
        (  select null
           from registrations 
           where registrations.class_id = classes.id
        ) 
/

Then there is the ANTI-JOIN:

select *  
from classes 
     full outer join registrations 
            on (classes.id = registrations.class_id )
where registrations.class_id is null 
/

Lastly there is the MINUS operator:

select id  
from classes 
minus
select class_id 
from registrations
/

This final syntax is quite speedy to write, but obviously falls down in the details. If we want more columns from CLASSES, columns which aren't in REGISTRATIONS, then we need to feed it as a sub-query to a select on CLASSES. So, it is not as widely applicable as the other syntaxes but useful on occasions.