Gintas_ Gintas_ - 3 months ago 9
MySQL Question

SQL efficiently select values where that value has a record in another table

There is a table

users
with fields
fbid
and
fcmtoken
. Then there is a table
friends
with fields
fbid
and
friendfbid
. So in
friends
table, to get all of my friends, it would have to be done like this(just to help you get the idea):

SELECT friendfbid FROM friends WHERE fbid = MYFBID


I need to make a query to get all fcmtokens of my friends. I made it like this and it seems to work:

SELECT
fbid,
fcmtoken
FROM
users
WHERE
EXISTS (
SELECT
friendfbid
FROM
friends
WHERE
fbid = ?
AND friendfbid = users.fbid
)


Is it efficient enough? It seems to create a lot of select queries, so that makes me think about it.

Answer

Try moving the WHERE fbid = ? into the outer query:

SELECT
    fbid,
    fcmtoken
FROM
    users
WHERE fbid = ?
AND EXISTS (
        SELECT
            *
        FROM
            friends
        WHERE
            friendfbid = users.fbid
    )

But a join might be more efficient:

SELECT
    u.fbid,
    u.fcmtoken
FROM users AS u
JOIN friends AS f
  ON f.friendfbid = u.fbid
WHERE f.friendfbid = ?