Query returning all posts by friends and friends of friends I'm currently representing friends in a table called relations, which has two columns (userA, userB) which are the user IDs of two users. If user 1 and user 3 are friends, I'd insert (1, 3) and (3, 1) into this table.

I also have a table called posts, which has, among other fields, an id and an ownerId.

So something like this:

relations:
 userA | userB
   1   |   3
   3   |   1
   3   |   4
   4   |   3
   4   |   2
   2   |   4

posts:
 id | ownerId | ...
  1 |    2    | ...
  2 |    3    | ...
  3 |    4    | ...


I'd like to select every post whose owner is a friend or friend of friend. 

For example, if the user the query was selecting for was userId=1, the query should return posts 2 and 3, since userId=3 is a friend, and userId=4 is a friend of a friend).

I've come up with the following, but was wondering if there are better ways to do this (that don't involve a UNION).

SELECT posts.id FROM posts
INNER JOIN relations ON relations.userA=[userId]
WHERE relations.userB=posts.ownerId
UNION
SELECT posts.id FROM posts
INNER JOIN relations AS r1 ON r1.userA=[userId]
INNER JOIN relations AS r2 ON r2.userA=r1.userB
WHERE r2.userB=posts.ownerId AND r2.userB&lt;&gt;[userId];
It is possible to solve this with a query that does not have a single UNION in it:

SELECT
  p.id
FROM
  relations AS r1
  INNER JOIN relations AS r2 ON r1 ON r1.userB = r2.userA
  INNER JOIN posts AS p ON p.ownerId IN (r1.userB, r2.userB)
WHERE
  r1.userA = @userID
  AND r2.userB &lt;&gt; @userID
;


The optimiser, however, might still internally rewrite your query to something very similar to a UNION query, so in the end the performance might not be much better, if at all, than that of query with an explicit UNION.