Comparing two tables in two different, but structurally identical databases is not returning expected results

Question

I am not sure how to approach this problem.

I am trying to find all the professors from the year 2020 database that are NOT in year 2019 database.

The two databases are structurally identical, it's just that some of the data is different.

If I run this query:

SELECT  * FROM year_2020.dbo.classList 
WHERE professorID NOT IN(SELECT professorID FROM year_2019.dbo.classList)

I get 0 results

Which I know is not right.

Because I can take a professorID that is 2020:

SELECT * FROM year_2020.dbo.classList WHERE classID = 'bb101' 

and I see the professorID of 'rts103' like this:

classID    dateStart     DeptId     professorID
bb101      2/1/2020      soc3       rts103

Then I can do the same thing for 2019:

SELECT * FROM year_2019.dbo.classList WHERE classID = 'bb101' 

And I see he's NOT in this database:

classID    dateStart     DeptId     professorID
bb101      2/1/2019      soc3       NULL

So I am not sure what's going on.

Is there a better way to formulate this query?

Thanks!

Answer 1

I would recommend not exists. Also, your question makes it look like you want to correlated rows on professorID and classID, so:

SELECT c20.* 
FROM year_2020.dbo.classList c20
WHERE NOT EXISTS (
    SELECT 1 
    FROM year_2019.dbo.classList c19
    WHERE c19.professorID = c20.professorID AND c19.classID = c20.classID
)