Difference between two recordsets in TSQL

Question

I am using Microsoft SQL Server 2008 and we have two tables called Applications and Enrolments. I want to know which applications are not converted into the Enrolments. As shown in the following figure.

I tried to use NOT IN but it works only with the single column. I have 2 columns to compare. Could you please advise me what is the most appropriate way? Thanks.

PS. I cannot change the structure of the database and they are from third party vendor.

Answer 1

Another way, using except

select  
        StudentID, 
        CourseID
FROM dbo.Applications
except
select  
        StudentID, 
        CourseID
FROM dbo.Enrolments

Answer 2

Use NOT EXISTS instead:

SELECT StudentID, CourseID
FROM dbo.Applications a
WHERE NOT EXISTS(
    SELECT 1 FROM Enrolments e
    WHERE e.StudenID = a.StudenID 
    AND   e.CourseID = a.CourseID
)

Answer 3

Try

SELECT a.*
FROM Applications a
LEFT JOIN Enrolments e 
       ON e.StudentId = a.StudentId
      AND e.CourseId = a.CourseId
WHERE e.StudentId IS NULL

Side note: interesting answer comparing similar solutions here

Answer 4

SELECT a.* FROM Applications a
LEFT JOIN Enrolments e 
   ON a.StudentID = e.StudentID AND a.CourseID = e.CourseID
WHERE e.StudentID IS NULL

Answer 5

Simplest way is probably a left join between applications and enrolments - returning just the ones where the enrolment is null

IE

SELECT a.*
FROM Applications a
LEFT JOIN Enrolments e ON a.StudentID = e.StudentID AND a.CourseID = e.CourseID
WHERE e.StudentID IS NULL