SQL Query - Single SELECT instead of UNION

Question

Building a social site which allows users to follow each other, and enforcing a business rule that only allows DMs when two users mutually follow each other.

Follows are stored in a lookup:

TABLE FOLLOWS
- userA_ID FK
- userB_ID FK

Given this dataset in the FOLLOWS table:

|userA_ID  |userB_ID  |
-----------------------
|123       |456       | <- Gerald follows Jane
|789       |456       | <- Grace follows Jane
|456       |789       | <- Jane follows Grace

If I'm logged in as Jane, I can DM Grace but not Gerald, and vice-versa.

My current Query....

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

...feels clunky. Is there a way to write this in a single SELECT instead of with a UNION?

Answer 1

Yes, you don't need to use UNION INTERSECT. The intersection is basically computed as a WHERE EXISTS(…) statement checking for the same values to be in the result of the second query. But the query to arrive at these results can be simplified a bit by using EXISTS directly - you don't need to join User again:

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"
  )

Btw you can also drop the DISTINCT (u.id) - if your primary keys / unique constraints on User(id) and Follows(userA_ID, userB_ID) are set up correctly then this query can't generate multiple rows for the same user.

And you can actually express that WHERE EXISTS(*) as another 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

Another approach to simplify the query is to move the intersection to a straightforward subquery specifically for mutual followers:

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