是的，你不需要使用UNIONINTERSECT。交集的计算基本上是一个WHERE EXISTS(…)语句，检查第二个查询的结果中是否有相同的值。但是通过直接使用EXISTS可以简化得到这些结果的查询-你不需要再次连接User：

SELECT DISTINCT (u.id) AS "userId", u."name" AS "userName"
FROM "Follows" x
INNER JOIN "User" u ON u.id = x."userA_ID"
WHERE x."userB_ID" = $1
  AND EXISTS (
    SELECT *
    FROM "Follows" y
    WHERE y."userA_ID" = $1 -- x."userB_ID", for more symmetry
      AND y."userB_ID" = x."userA_ID"
  )

顺便说一句，你也可以删除DISTINCT (u.id)--如果你在User(id)和Follows(userA_ID, userB_ID)上的主键/唯一约束设置正确，那么这个查询就不能为同一个用户生成多行。
实际上，你可以将WHERE EXISTS(*)表示为另一个JOIN：

SELECT DISTINCT (u.id) AS "userId", u."name" AS "userName"
FROM "Follows" x
INNER JOIN "Follows" y ON (y."userA_ID" = x."userB_ID" AND y."userB_ID" = x."userA_ID")
INNER JOIN "User" u ON u.id = x."userA_ID"
WHERE x."userB_ID" = $1

另一种简化查询的方法是将交集移动到一个直接的子查询，专门用于共同的关注者：

SELECT u.id AS "userId", u.name AS "userName"
FROM "User" u
JOIN (
  SELECT "userA_ID", "userB_ID" FROM "Follows"
  INTERSECT
  SELECT "userB_ID", "userA_ID" FROM "Follows"
) AS mutual(a, b) ON mutual.a = u.id
WHERE mutual.b = $1

您需要的是一个inner join，而人们大多不知道的是，您并不局限于一个简单的a.id = b.a_id

答案

select left_side.userA_ID as user_one, left_side.userB_ID as user_two
from follows as left_side 
    inner join follows as right_side on 
        left_side.userA_ID = right_side.userB_ID and 
        left_side.userB_ID = right_side.userA_ID

并得到

user_one | user_two
----------+----------
      456 |      789
      789 |      456
(2 rows)

至于如何只做一行，我还不知道，如果你需要这个生产，你可以离开它，虽然。使它不同不会更快。

解释

我做得很快

CREATE TABLE follows (
  userA_ID INTEGER NOT NULL,
  userB_ID INTEGER NOT NULL,
  PRIMARY KEY (userA_ID, userB_ID)
);
INSERT INTO follows (userA_ID, userB_ID) VALUES
  (123, 456),
  (789, 456),
  (456, 789);

你的主要问题

select * 
from follows as left_side 
    inner join follows as right_side on 
        left_side.userA_ID = right_side.userB_ID and 
        left_side.userB_ID = right_side.userA_ID

结果：

usera_id | userb_id | usera_id | userb_id
----------+----------+----------+----------
      789 |      456 |      456 |      789
      456 |      789 |      789 |      456
(2 rows)

你可以稍微修理一下，不过没关系

您的要求可以按照每个共同的追随者进行翻译，在表“追随者”中将有1个以上的相关记录。

SELECT u.id AS userId, u."name" AS "userName"
FROM   "User" u 
WHERE 1 < (
    SELECT COUNT(1)
    FROM "Follows" f
    WHERE  ((f."userA_ID" = $1 AND u.id = f."userB_ID")
         OR (f."userB_ID" = $1 AND u.id = "userA_ID"))