Page Recommendations

Problem

Recommend pages to user_id = 1: every page liked by any of user 1's friends, minus pages user 1 already likes, with no duplicates. Two tables drive it.

Table Friendship — primary key (user1_id, user2_id). Each row is one undirected friendship, but it is stored in one of two orientations (sometimes 1 is in the left column, sometimes the right).

+-----------+-----------+
| user1_id  | user2_id  |
+-----------+-----------+
| 1         | 2         |
| 1         | 3         |
| 2         | 3         |
| 4         | 1         |   <- user 1 is on the RIGHT here
| 3         | 4         |
+-----------+-----------+

Table Likes — primary key (user_id, page_id).

+---------+---------+
| user_id | page_id |
+---------+---------+
| 1       | 88      |   <- user 1 already likes 88
| 2       | 23      |
| 2       | 24      |
| 3       | 24      |   <- 24 again (dup across friends)
| 3       | 56      |
| 3       | 33      |
| 4       | 33      |   <- 33 again (dup across friends)
| 4       | 77      |
| 4       | 88      |   <- 88, but user 1 has it -> exclude
+---------+---------+

Mechanism

Because each friendship is stored in only one column orientation, you must scan Friendship from both ends — collect user2_id where user1_id=1 and user1_id where user2_id=1 — then pull every page those friends like and subtract the set user 1 already likes.

The query

WITH friends AS (
    SELECT user2_id AS friend FROM Friendship WHERE user1_id = 1
    UNION ALL
    SELECT user1_id AS friend FROM Friendship WHERE user2_id = 1
)
SELECT DISTINCT page_id AS recommended_page
FROM Likes
WHERE user_id IN (SELECT friend FROM friends)
  AND page_id NOT IN (
      SELECT page_id
      FROM Likes
      WHERE user_id = 1
  );

UNION ALL (not UNION) is correct here: the two branches read disjoint rows of the same table, so they cannot collide, and skipping the dedup pass is cheaper. Any genuine duplicate friend is harmless — user_id IN (...) only tests membership, and the final DISTINCT collapses pages anyway.

Worked trace

Run it against the data above, step by step. Note how each value comes from a real row.

1. friends CTE. Left branch: rows (1,2),(1,3) give 2, 3. Right branch: row (4,1) gives 4. UNION ALL -> friend = {2, 3, 4}. (Row (2,3) and (3,4) never match — neither column is 1.)
2. Pages liked by friends (user_id IN {2,3,4}), as raw rows before any dedup: 23, 24 (user 2); 24, 56, 33 (user 3); 33, 77, 88 (user 4). Bag = [23, 24, 24, 56, 33, 33, 77, 88].
3. Subtract user 1's likes. User 1 likes {88}. NOT IN drops every 88: bag becomes [23, 24, 24, 56, 33, 33, 77]. This is the row that vanishes — and now you can see why (88 came from user 4, but user 1 owns it).
4. DISTINCT. Collapses the two 24s and the two 33s. Final result, any order: {23, 24, 56, 33, 77}.

The DISTINCT is load-bearing here, not decoration: 24 and 33 each arrive from two different friends, so without it the answer would contain duplicate recommendations and fail the "without duplicates" requirement.

Pitfalls

1. NOT IN + a NULL in the subquery silently returns nothing

This is the real lesson of the problem, and it bites in production, not on LeetCode's clean data. If the exclusion subquery ever yields a NULL — say page_id is nullable and one row has a NULL — then page_id NOT IN (88, NULL) evaluates to UNKNOWN for every row, never TRUE, so the whole query returns zero rows. The reason: x NOT IN (a, NULL) expands to x <> a AND x <> NULL, and x <> NULL is UNKNOWN, and TRUE AND UNKNOWN = UNKNOWN. The filter quietly fails closed and your recommendations disappear with no error.

Harden it with NOT EXISTS, which is NULL-safe (it tests row existence, not value equality):

SELECT DISTINCT l.page_id AS recommended_page
FROM Likes l
WHERE l.user_id IN (SELECT friend FROM friends)
  AND NOT EXISTS (
      SELECT 1 FROM Likes me
      WHERE me.user_id = 1
        AND me.page_id = l.page_id
  );

2. Scanning only one direction of Friendship

If you write just WHERE user1_id = 1 you silently miss every friendship stored as (4,1) — here you'd lose friend 4 and never recommend pages 33, 77 (and 88). The undirected edge lives in one orientation only; you must read both columns.

3. UNION vs UNION ALL

Both produce a correct final answer here. Prefer UNION ALL: the two branches read disjoint rows, so UNION's dedup sort is pure overhead. Reach for UNION only when the branches can genuinely overlap and you need the dedup.

Takeaways

• An undirected relationship stored once must be queried from both endpoints — two SELECTs glued with UNION ALL, aliasing the partner column to one name.
• NOT IN is a trap whenever the inner list can contain NULL: it returns no rows, silently. Use NOT EXISTS (or filter the NULLs) for set-difference against nullable columns.
• The final DISTINCT earns its keep: the same page reaches you through multiple friends, and the spec forbids duplicates.
• UNION ALL over UNION when branches can't collide — skip the dedup you don't need.

Problem from LeetCode 1264 "Page Recommendations." Mechanism, the both-directions diagram, the internally-consistent worked dataset, and the NOT IN/NULL three-valued-logic analysis follow the SQL-92/PostgreSQL semantics documented in the PostgreSQL manual ("Subquery Expressions" and "Comparison Functions and Operators") and Markus Winand's SQL Performance Explained. Re-authored and deepened for this guide: the original walkthrough dropped page 88 in Step 3 without ever showing user 1's likes and ran a no-op DISTINCT in Step 4; both are now traced against real rows, and the NULL pitfall that was missing has been added.