exclude records that exist in another table

Question

I got 2 tables like these:

emails:
emailID int(10) auto_increment, memberID int(10), emailData text, and so on

members:
memberID int(10) auto_increment, user_name char(40), password char(50), and so on

My query is this:

select 
emails.emailID, emails.emailData, 
members.memberID, members.user_name 
from emails, members where
emails.memberID = members.memberID

Now I've added two more tables like these:

blocked:
id int(10) auto_increment, memberID int(10), blocked_memberID int(10)

markedAsRead:
id int(10) auto_increment, memberID int(10), emailID int(10)

I want to modify my original query so that it excludes memberID which are in blocked.blocked_memberID and also excludes emailID which are in markedAsRead.emailID

How can I do this?

Duplicate https://stackoverflow.com/questions/4560471/how-to-exclude-rows-that-dont-join-with-another-table — Dmitry, Sep 03 '17 at 08:08
Possible duplicate of [How to exclude rows that don't join with another table?](https://stackoverflow.com/questions/4560471/how-to-exclude-rows-that-dont-join-with-another-table) — Dmitry, Sep 03 '17 at 08:09

sagi · Accepted Answer · 2017-09-03T08:43:16.237

1

You can use NOT EXISTS :

SELECT ....
FROM   .... 
WHERE  ..... // Replace the dots with Your Query
  AND NOT EXISTS(SELECT 1 FROM blocked
                 WHERE emails.memberID = blocked.memberID)
  AND NOT EXISTS(SELECT 1 FROM markedAsRead
                 WHERE emails.emailID = markedAsRead.emailID)

You could also lookup for LEFT JOINS or NOT IN to exclude records that doesn't exists in a particular table.

EDIT: Usually EXISTS() and LEFTJOIN have similar performaces, sometime it can even perform better than a join.

LEFT JOIN sulotion:

SELECT ...
FROM ...
LEFT JOIN blocked 
 ON(WHERE emails.memberID = blocked.memberID)
LEFT JOIN markedAsRead
 ON(emails.emailID = markedAsRead.emailID)
WHERE ...
 AND blocked.memberID IS NULL
 AND markedAsRead.emailID IS NULL

edited Sep 03 '17 at 08:43

answered Sep 03 '17 at 08:14

sagi

40,026
6
59
84

Hi, in the not exists link you posted, it says that the subquery will be run for each record that matches the primary query. If the table becomes large, then will this become a major problem, specially if secondary tables are also large? If so, can you also please post an example with the left join you mentioned? Thanks – Sumit Kumar Sep 03 '17 at 08:32
See edit. As long as your tables are indexed properly, there shouldn't be a problem or a major difference between the two, but I've also included a left join solution . – sagi Sep 03 '17 at 08:47
Thank you sooo much :) Like you mentioned if performance is similar, so I'll go with the not exists method. That seems much simpler to understand. Thanks again :) – Sumit Kumar Sep 03 '17 at 09:31

exclude records that exist in another table

1 Answers1