查询Joe未关注用户的帖子：LeftJoin SQL语句失效问题排查

Hey Joe, let's get this sorted out! First, I'll make some reasonable assumptions about your table structures since you didn't share them—if these don't match your actual schema, just adjust the column names accordingly:

Assumed Table Structures #

• Users: id_user (primary key), username, and other user-related columns
• Community: Tracks follow relationships, with follower_id (the ID of the user doing the following) and followee_id (the ID of the user being followed)
• Posts: id_post, user_id (foreign key to Users.id_user), content, and other post details

The goal is to fetch posts from users that Joe (id_user=1) doesn't follow, and optionally exclude Joe's own posts (I'll include that as a filter you can remove if needed).

Fix 1: Using NOT IN (Simple & Straightforward) #

This approach first gets all user IDs that Joe follows, then selects posts from users not in that list:

SELECT p.*, u.username AS author_username
FROM Posts p
JOIN Users u ON p.user_id = u.id_user
WHERE p.user_id NOT IN (
    -- Get all users Joe follows
    SELECT followee_id FROM Community WHERE follower_id = 1
)
AND p.user_id != 1; -- Remove this line if you want to include Joe's own posts

Fix 2: Correct LEFT JOIN Approach (Since Your Original Attempt Failed) #

Your left join probably didn't work because you might have placed the follower_id = 1 condition in the WHERE clause instead of the ON clause (which turns it into an inner join). Here's the correct version:

SELECT p.*, u.username AS author_username
FROM Posts p
JOIN Users u ON p.user_id = u.id_user
-- Left join posts' authors to Joe's follow list
LEFT JOIN Community c 
  ON p.user_id = c.followee_id 
  AND c.follower_id = 1 -- This condition stays in the ON clause!
WHERE c.followee_id IS NULL -- Only keep authors not in Joe's follow list
AND p.user_id != 1; -- Optional: exclude Joe's own posts

Quick Troubleshooting Tip #

If your Community table has duplicate follow entries (e.g., Joe accidentally followed someone twice), add DISTINCT to the subquery in the NOT IN version to make it more efficient:

SELECT DISTINCT followee_id FROM Community WHERE follower_id = 1

Let me know if you need to adjust this to match your actual table columns!

内容的提问来源于stack exchange，提问作者joe