find `accounts` with whom user haven't yet made friendship

I have a table accounts (id, full_name) and account_friends (account_id, friend_id). friend_id is basically account id of another user. How开发者_如何学运维 do I find all accounts with whom account id=1 haven't made friendship yet?

In response to @OMG Ponies.

Basically, I do understand that this will require multiple joins. My logic goes like this:

SELECT
    a2.id
FROM
    accounts a1

/* left/right join a1 with account_friends */

INNER JOIN
    accounts a2
ON
    af1.friend_id = a2.id
WHERE
    a1.id;

Though I don't understand the left/right join bit that should do the trick of find the not-friend entries.

This will give you all the account ids and names where the id is not account id=1 and not a friend of account id=1:

SELECT id, full_name
  FROM accounts
 WHERE id NOT IN (SELECT friend_id
                    FROM account_friends
                   WHERE account_id = 1)
   AND id <> 1

As antonis_wrx mentioned in the comments, this above query will only hold true if all friendships for an account are stored account->friend (i.e. (1,2)) and not a mixture i.e. (1,2) and (3,1).

Here is a query that will work for the second case:

SELECT id, full_name
  FROM accounts
 WHERE id NOT IN (SELECT friend_id FROM account_friends WHERE account_id = 1)
   AND id NOT IN (SELECT account_id FROM account_friends WHERE friend_id = 1)
   AND id <> 1