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

I think you need an additional column to indicate status, don't really need two tables. — ajreal, Dec 08 '11 at 17:33
@ajreal That is a good point. Perhaps I will consider tweaking the schema in the way you suggested. Appreciate the tip! — PFranchise, Dec 08 '11 at 17:37
I prefer 2 tables over one table and a status column, 99% of the time. And 11 tables over one table and 10 status columns. It's not easy to optimize a query that searches one or many status columns (in MysQL). — ypercubeᵀᴹ, Dec 09 '11 at 05:32

ypercubeᵀᴹ · Accepted Answer · 2016-01-20T11:54:31.110

96

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.

edited Jan 20 '16 at 11:54

answered Dec 08 '11 at 17:34

ypercubeᵀᴹ

113,259
19
174
235

Awesome! Thank you very much. I did not know you could use Where on two fields in that way, but was hopeful that that would be an option. Thanks again and have a great day! – PFranchise Dec 08 '11 at 17:36
Following my tests, the NOT EXISTS version is faster than the NOT_IN version – Ka. Jan 20 '16 at 11:36
@Ka. yes, the `NOT IN` with a tuple is not optimized as good as `NOT EXISTS`. Which version did you test with? I haven't tested if they have improved the optimizer in the new 5.7 version. – ypercubeᵀᴹ Jan 20 '16 at 11:52

score 5 · Answer 2 · answered Dec 08 '11 at 17:33

5

Is this what you need?

select ct.* from
completedTasks ct
left outer join plannedTasks pt on ct.taskId = pt.TaskId and ct.userId = pt.userId
where pt.taskId is null

However, I agree with the comment - given what we know from the question a status column sounds like a better schema than two tables.

answered Dec 08 '11 at 17:33

jmacinnes

1,589
1
11
21

A status column can be overlooked, not updated, set to the wrong value, etc. A join like this will provide the desired results regardless of the status of the status column. – Bob Jarvis - Слава Україні May 10 '20 at 20:37

score 1 · Answer 3 · answered Aug 22 '17 at 17:23

1

@ypercubeᵀᴹ Thanks for sharing below mention query

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

My problem solved.

answered Aug 22 '17 at 17:23

Jagdeep Singh

319
3
7

This should be a comment, not an answer :) – sniperd Aug 22 '17 at 18:00
Actually, I made some change with reference to @ypercube shared query. Will take care in future. Thanks – Jagdeep Singh Mar 02 '18 at 07:19
I guess this is the best way. it works fine for me. Thank you – Mohammad H. May 22 '19 at 11:07

score 0 · Answer 4 · edited May 10 '20 at 20:34

0

the first answer pretty good, It did work for me, just was missing a ")" after PlannedTasks. I need to weed out the elements from one table, that were in the other, so...

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

This is my code:

$query_C_Ranking = sprintf("SELECT * 
                                FROM tblpinturas
                                WHERE (idCode) NOT IN 
                                        (SELECT idCode FROM tblranking)
                                ");

edited May 10 '20 at 20:34

codedge

4,754
2
22
38

answered May 10 '20 at 20:21

M Silva

1

For the SO platform to work correctly, existing Answers should be upvoted, not duplicated. If there is a typo, either add Comment below the post, or suggest. The SO platform operates in a different way than forums do. But that is part of the value of this platform. Each platform has is strengths. Also, when referencing another post (Q or A), it needs to be linked to, as there is no other easy way to tell which post you're referring to. The order of Answers changes, as the number of Answers, and votes for each changes. It also depends on the "sort" order each user has chosen. – SherylHohman May 11 '20 at 01:36

mysql "Where not in" using two columns

4 Answers4

Linked