Sql function to remove values from follower table that are common in unfollow table

Question

I want to calculate total followers of a person from follow and unfollow table using SQl. Form of table are as follows

Follow table:

Unfollow table:

My tries:

1)

DELETE FROM follow_table
WHERE follow_table.userid IN (SELECT unfollow_table.userid FROM     unfollow_table) and 
follow_table.Followerid IN (SELECT unfollow_table.Followerid FROM unfollow_table);

2. SELECT userid , Followerid
   FROM follow_table
   MINUS
   SELECT userid , Followerid
   FROM unfollow_table;
   

I also tried union and left join but no luck any suggestion are welcome.

Answer 1

I think the delete you want is

DELETE FROM follow_table 
  WHERE (userid, followerid) IN 
  (SELECT userid, followerid
   FROM unfollow_table);

This will delete the row with the userid, followerid pair in follow_table where there is a matching combination of userid, followerid in the unfollow_table. You may have to turn off safe updates if you do not have the userid, followerid pair set as a unique key.

Then the select below should be the one you want, where the "unfollowers" are removed.

SELECT *
FROM follow_table;

Note, this solution should work in MySQL but might not be valid in other DBMSs. You may have to use an EXIST subquery like the one shown below.

DELETE
FROM follow
WHERE EXISTS
  (SELECT userid, followerid
   FROM unfollow
   WHERE follow.userid = unfollow.userid
     AND follow.followerid = unfollow.followerid);