How can I query from two tables but get results only from one?

Question

I have list of contacts and then I have list of blocked contacts. I need to query contacts that are not blocked. How can I make query where all blocked contacts are filtered out inside the sql query?

Answer 1

PSEUDO SQL:

SELECT * FROM CONTACTS as c WHERE c.ID NOT IN (SELECT ID FROM BLOCKEDCONTACTS)

Answer 2

Another possible solution to this would be:

SELECT     *
FROM       TblContacts AS C
LEFT JOIN  TblBlockedContacts AS BC
ON         C.ID = BC.ID
WHERE      C.ID IS NULL

I think this should be more performant than a NOT IN.

Answer 3

I can suggest another variant:

SELECT     * 
FROM       CONTACTS as c 
WHERE      NOT EXISTS (SELECT    * 
                       FROM      BLOCKEDCONTACTS as B 
                       WHERE     c.id=b.id)

Choosing this variant or any of two others are dependent on DBMS and indexes you use.

Answer 4

You can follow johan's code or instead alter your db. Instead of 2 tables, just make it one. And just add another field. You can name it status with a enum type("blocked, unblocked");

Then perform this:

Select * from contacts where status = 'unblocked';

In this way, you would give the sql processor easy work because it only accesses one table instead of joining 2 or more.