SQL - SELECT * FROM posts WHERE id_user IN SUBQUERY?

Question

I have to select all posts posted by the users listed in my SUBQUERY - SUBQUERY is a table with only one column - user IDs. The purpose is to get all posts of user's "friends"

I have a misty memory that you can use IN on SELECT
but the SELECT's result can only be one column only - am I right?

So this query:

SELECT * FROM posts WHERE id_user 
IN (SELECT id_friend as id_user FROM friends WHERE id_user=:some_id_user)

Can it be done? If so, is there a more efficient way?

BTW: I am using MySQL

Well it is nice that is seems :)), but have you actually tested it in comparsion to other possibilities - **are there even any other possibilites?** — jave.web, Feb 07 '14 at 20:18

score 4 · Accepted Answer · edited May 23 '17 at 12:03

4

When it comes in between JOIN and subquery, JOIN is faster. Though it depends on scenario, in this case, JOIN would be better.

Use INNER JOIN

SELECT * 
FROM 
    posts P
INNER JOIN 
    friends F ON P.id_user = F.id_friend 
WHERE F.id_user  = :some_id_user

See this for more info : Stackoverflow :: Join vs. sub-query

edited May 23 '17 at 12:03

Community

1
1

answered Feb 07 '14 at 20:20

hsuk

6,770
13
50
80

NB - There are a number of unexpected cases in DB2 when `WHERE EXISTS(...)` is faster. – Hogan Feb 07 '14 at 20:55
@Hogan : what is DB2 ? – hsuk Feb 07 '14 at 21:03
@Hogan and #hsuk - and what if the SUBQUERY is getting IDs from different tables? You know like there are some other connections than friends? – jave.web Feb 08 '14 at 11:10
1

join those tables too – hsuk Feb 08 '14 at 11:32

score 3 · Answer 2 · answered Feb 07 '14 at 20:20

3

You can do this with an in statement as you describe or an inner join. For simple query like this I would expect the performance to be the same.

SELECT * 
FROM posts 
WHERE id_user IN (SELECT id_friend FROM friends WHERE id_user=:some_id_user)

OR (similar to your query)

SELECT * 
FROM posts P
JOIN friends F ON P.id_user = F.id_friend AND  F.id_user = :some_id_user

answered Feb 07 '14 at 20:20

Hogan

69,564
10
76
117

@hsuk - did not, I beat you by **ONE MINUTE** (check the timestamps!) :D – Hogan Feb 07 '14 at 20:54
@hsuk - my answered lines are 1 min different. – Hogan Feb 07 '14 at 20:56
go to my profile and browse recent questions, u will find unanswered tsql questions over der :) Leave these basic questions for we amateurs :) – hsuk Feb 07 '14 at 20:57
@hsuk - I checked -- all your questions already have all the answers I know... I guess you are stuck with me harassing you. But I did give you a +1 even if I was first by timestamp. – Hogan Feb 07 '14 at 21:00

SQL - SELECT * FROM posts WHERE id_user IN SUBQUERY?

2 Answers2