Check if one row exists in two tables

Question

I have two tables:

friends:

accountId | friendId
--------------------
       A1 |      B15
       A1 |      C34
       A1 |      D48

and followers

accountId | followerId
----------------------
       A1 |        B15
       A1 |        C34

I try to run a MYSQL query that will return a "friendId" that does not exist in "followerId" column in the other table. Basically i try to find, which rows from the "friends" table, do not exist in the "followers" table.

I have tried:

SELECT *
FROM friends AS f
LEFT JOIN followers AS l
ON f.accountId = l.accountId
WHERE l.accountId IS NULL

but it return nothing. No result. Just null. I have checked the validity of the tables manually, and it does actually contain such entries. Any ideas?

Answer 1

You could use:

SELECT *
FROM friends AS f
LEFT JOIN followers AS l
  ON f.accountId = l.accountId
 AND f.friend_id = l.follower_id   -- additional condition
WHERE l.accountId IS NULL;

Output:

accountId | friendId | accountId | follower_id
       A1 |      D48 | NULL      | NULL