MySql how to combine LEFT JOIN query with NOT IN query

Question

I need to combine LEFT JOIN query with NOT IN query and have to get the result values from these queries. I have get the correct results while using these two queries at separately.

I have two tables likely user and answer

user table

user_id 
1
2
3
4
5

answer table

user_id     date 
1           2015-10-15 21:23:14
2           2015-10-15 20:23:14
3           2015-11-11 16:23:14

LEFT JOIN query:

SELECT user.user_id
FROM user 
LEFT JOIN answer 
  ON user.user_id = answer.user_id 
WHERE DATEDIFF(NOW(),answer.date) > 5

This query returns result user_id 1,2.

NOT IN query:

SELECT user.user_id 
FROM user 
WHERE user.user_id NOT IN (SELECT answer.user_id FROM answer)

This query returns result user_ids 4, 5.

I need to combine this two queries into single query so I tried with these two below queries:

 SELECT user.user_id 
 FROM user 
 LEFT JOIN answer 
   ON user.user_id = answer.user_id 
 WHERE (DATEDIFF(NOW(),answer.date) > 5 
   AND user.user_id NOT IN (SELECT answer.user_id FROM answer))

and

 SELECT user.user_id 
 FROM user 
 LEFT JOIN answer 
   ON user.user_id = answer.user_id 
 WHERE user.user_id NOT IN (SELECT answer.user_id FROM answer) 
   AND DATEDIFF(NOW(),answer.date) > 5

But these return empty user_id.

Edit

Expected result should contain values of 1,2,4,5

What do you expect to see as output? – shree.pat18 Nov 12 '15 at 10:16 — shree.pat18, Nov 12 '15 at 10:16
shouldn't that be `WHERE DATEDIFF(NOW(),answer.date) < 5` ? – joop Nov 12 '15 at 14:49 — joop, Nov 12 '15 at 14:49

score 2 · Accepted Answer · answered Nov 12 '15 at 10:16

2

If your meaning is about to combine 2 selection together
Try Union All / Union :

SELECT * FROM 
(
SELECT [WITH YOUR LEFT JOIN STATEMENT]
UNION 
SELECT [WITH YOUR NOT IN STATEMENT]
) ResultTABLE

answered Nov 12 '15 at 10:16

ZenithS

987
8
20

This is how we emulated `LEFT JOINS` in the past, before SQL92 introduced them. – joop Nov 12 '15 at 13:02

joop · Answer 2 · 2015-11-12T17:50:47.027

1

If you refer to the answer table in the WHERE clause, the answer.date will be restricted to non-NULL value, making the LEFT JOIN behave like a regular join. Move the condition to the ON condition to retrieve all the users, plus maybe the matching rows in answers:

 SELECT user.user_id
 FROM user u
 LEFT JOIN answer a ON u.user_id = a.user_id
                    AND DATEDIFF(NOW(),a.date) < 5
    ;

Edit: after the edit of the question it appears the OP wants joined records with non existing answer OR answer.date too old/young:

 SELECT user.user_id
 FROM user u
 LEFT JOIN answer a ON u.user_id = a.user_id
 WHERE a.date IS NULL               -- no answer record
    OR DATEDIFF(NOW(),a.date) > 5   -- too old answer record
    ;

Final version: Since the OP wants to find users who don't have a (recent) answer, the query can be simplified to:

SELECT user.user_id
FROM user u
WHERE NOT EXISTS (
    SELECT * FROM answer a
    WHERE a.user_id = u.user_id
    AND DATEDIFF(NOW(), a.date) <= 5
    );

edited Nov 12 '15 at 17:50

answered Nov 12 '15 at 10:23

joop

4,330
1
15
26

1

I.e. with the answer table's condition in the WHERE clause like that, the LEFT JOIN executes as a regular INNER JOIN. – jarlh Nov 12 '15 at 10:26
Exactly! (I'll add that) – joop Nov 12 '15 at 10:28
@joop your edited answer returns `1,2,4,5` but I need `3,4,5` – Manoj Nov 12 '15 at 16:20
sorry actually my `left join` query returning values of `1,2` but I thought that `3` alone. – Manoj Nov 12 '15 at 16:52
That is because the `greater than` sign is flipped (see my comment on the question) I'll fix it for you. – joop Nov 12 '15 at 17:35

MySql how to combine LEFT JOIN query with NOT IN query

2 Answers2