I have a User table
/ id_user / username / ..
friends table (pair AB and BA):
/ friend_of / friend_to
and a friend_request table (used for pending friend requests)
/ id_req/ sent_from / sent_to
I would like to have a little search for new friend feature (autosuggestion style) and I would like NOT to show in the list friends that are ALREADY in my friends list OR being in a friend request pending.
The idea behind the query would be to show the username of user NOT in the friend_to column and NOT in the sent_to column.
I came up with these two flavors:
select id_user,username from public.user u where username like '%tom%' and u.id_user NOT IN (SELECT sent_to FROM friend_request where sent_from = 288) AND u.id_user NOT IN (SELECT friend_to FROM friends where friend_of=288);
select id_user,username from public.user u left join friend_request fr on u.id_user=sent_to AND fr.sent_from=288 left join friends f on u.id_user = f.friend_to AND f.friend_of=288 where username like '%tom%' AND sent_to IS NULL and friend_to IS NULL;
Both of these produce the same result. But I would like some advice on which one is a better choice performance and bets practice wise?