mysql "Where not in" using two columns

Question

I have one temporary table that contains userID and taskID. It is called CompletedTasks.
I have a second table that contains userID and taskID. It is called PlannedTasks.

I need to get a list of all taskIDs that were completed, but not planned.
So, I need to somehow weed out from completed tasks, all rows where both:

PlannedTasks.userID != CompletedTasks.userID 

AND

PlannedTasks.taskID != CompletedTasks.taskID

Answer 1

You can use this (more compact syntax):

SELECT *
FROM CompletedTasks
WHERE (userID, taskID) NOT IN
      ( SELECT userID, taskID
        FROM PlannedTasks
      ) ;

or the NOT EXISTS version (which although more complex, should be more efficient with proper indexes):

SELECT c.*
FROM CompletedTasks AS c
WHERE NOT EXISTS 
      ( SELECT 1
        FROM PlannedTasks AS p
        WHERE p.userID = c.userID
          AND p.taskID = c.taskID
      ) ;

and of course the LEFT JOIN / IS NULL version that @jmacinnes has in his answer.