I have two tables posts and comments. Table comments have post_id attribute. I need to get all posts with type “open”, for which there are no comments with type “good” and created date MAY 1.
Is it optimal to use such SQL-query:
SELECT posts.* FROM posts WHERE NOT EXISTS ( SELECT comments.id FROM comments WHERE comments.post_id = posts.id AND comments.comment_type = 'good' AND comments.created_at BETWEEN '2010-05-01 00:00:00' AND '2010-05-01 23:59:59')
I’m not sure that NOT EXISTS is perfect construction in this situation.
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
That’s why the best way to search for missing values in MySQL is using a LEFT JOIN / IS NULL or NOT IN rather than NOT EXISTS.
Your query rewritten using
NOT IN could look like this:
SELECT * FROM posts WHERE posts.id NOT IN (SELECT post_id FROM comments WHERE comments.comment_type = 'good' AND comments.created_at BETWEEN '2010-05-01 00:00:00' AND '2010-05-01 23:59:59')
No idea whether it is faster, you could check it out:
SELECT * FROM posts LEFT JOIN comments ON comment.postid = post.id AND comment.comment_type='good' WHERE comment.postid IS NULL
Assuming postid is never NULL / a non NULLable column.