MySQL技术问题：如何关联两张表获取用户34未拥有的技能？

Got it, let's walk through this clearly—this is a super common SQL pattern once you get the hang of the join logic. First, let's assume your schema looks like the typical setup for this scenario (adjust column/table names to match your actual data):

• skills: Stores all available skills (columns like skill_id, skill_name)
• user_skills: Junction table linking users to their skills (columns like user_id, skill_id)

First, Let's Recap Your Existing Query (For Owned Skills) #

I’m guessing you have something like this to get the skills user 34 already has:

SELECT s.skill_id, s.skill_name
FROM skills s
INNER JOIN user_skills us 
  ON s.skill_id = us.skill_id
WHERE us.user_id = 34;

That works fine for owned skills, but to flip it to unowned skills, we need to adjust how we join and filter.

The Fix: Use LEFT JOIN + IS NULL Check #

The trick is to keep all skills from the skills table, then try to match them to user 34’s entries in user_skills. Any skill that doesn’t have a match (meaning user 34 doesn’t own it) will have NULL values in the user_skills columns—we filter those out.

Here’s the query:

SELECT s.skill_id, s.skill_name
FROM skills s
LEFT JOIN user_skills us 
  ON s.skill_id = us.skill_id 
  AND us.user_id = 34  -- Critical: Put the user filter HERE, not in the WHERE clause!
WHERE us.skill_id IS NULL;

Why This Works:

• LEFT JOIN ensures every row from skills is included in the result, even if there’s no matching row in user_skills.
• By adding us.user_id = 34 to the join condition (not the WHERE clause), we only look for matches specific to user 34. If a skill isn’t linked to user 34, the us.skill_id column will be NULL.
• The WHERE us.skill_id IS NULL clause then picks out exactly those unowned skills.

Why Your Previous Attempt Might Have Failed #

If you tried putting us.user_id = 34 in the WHERE clause instead of the join, you’d turn the LEFT JOIN into an INNER JOIN (since WHERE filters out all rows where us.user_id is NULL). That’s why it only returned owned skills, even when you tried to "flip" the logic.

Alternative: Using NOT EXISTS (If You Prefer) #

Another reliable method is using a subquery with NOT EXISTS—it’s often just as efficient and might feel more intuitive to some:

SELECT s.skill_id, s.skill_name
FROM skills s
WHERE NOT EXISTS (
  SELECT 1 
  FROM user_skills us 
  WHERE us.skill_id = s.skill_id 
    AND us.user_id = 34
);

This checks for each skill: "Is there no entry in user_skills linking this skill to user 34?" If yes, it includes the skill in the result.

Just swap out the table/column names to match your actual schema, and either of these methods will work regardless of how many skills the user already owns.

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