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
– shree.pat18

2015-11-12 10:16:10 +00:00
Commented Nov 12, 2015 at 10:16 — shree.pat18
– shree.pat18, Commented Nov 12, 2015 at 10:16
shouldn't that be WHERE DATEDIFF(NOW(),answer.date) < 5 ?

joop
– joop

2015-11-12 14:49:21 +00:00
Commented Nov 12, 2015 at 14:49 — joop
– joop, Commented Nov 12, 2015 at 14:49

ZenithS · Accepted Answer · 2015-11-12 10:16:30Z

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, 2015 at 10:16

ZenithS

1,01710 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

joop Over a year ago

This is how we emulated LEFT JOINS in the past, before SQL92 introduced them.

joop · Accepted Answer · 2015-11-12 17:50:47Z

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, 2015 at 17:50

answered Nov 12, 2015 at 10:23

joop

4,5431 gold badge18 silver badges26 bronze badges

5 Comments

jarlh Over a year ago

I.e. with the answer table's condition in the WHERE clause like that, the LEFT JOIN executes as a regular INNER JOIN.

joop Over a year ago

Exactly! (I'll add that)

Manoj Over a year ago

@joop your edited answer returns 1,2,4,5 but I need 3,4,5

Manoj Over a year ago

sorry actually my left join query returning values of 1,2 but I thought that 3 alone.

joop Over a year ago

That is because the greater than sign is flipped (see my comment on the question) I'll fix it for you.

Collectives™ on Stack Overflow

MySql how to combine LEFT JOIN query with NOT IN query

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related