SQL NOT IN运算符无法正常工作

I have the following tables:

        industries
        -----------
        + id
        + name

        users
        -----------
        + id
        + name

        teams
        -----------
        + id
        + name
        + type

        user_to_team
        -----------
        + id
        + user_id
        + team_id
        + industry_id

I am running the following sql query:

        SELECT teams.id
        FROM teams 
        LEFT JOIN user_to_team ON user_to_team.`team_id` = teams.id 
        WHERE teams.type = 'general' 
        AND (user_to_team.industry_id NOT IN(1))
        GROUP BY user_to_team.team_id 
        LIMIT 1

The problem is that it's returning teams that contain an industry_id of 1 in the user_to_team table.

You can use LEFT JOIN / IS NULL

SELECT teams.id
FROM teams 
  LEFT JOIN user_to_team 
    ON  user_to_team.team_id = teams.id
    AND user_to_team.industry_id IN (1)    -- caution: this is IN
                                           -- and **not** NOT IN
WHERE teams.type = 'general' 
  AND user_to_team IS NULL ;

or a NOT EXISTS subquery, which is easier to read:

SELECT teams.id
FROM teams 
WHERE teams.type = 'general' 
  AND NOT EXISTS
      ( SELECT *
        FROM user_to_team 
        WHERE user_to_team.team_id = teams.id 
          AND user_to_team.industry_id IN (1)       -- caution: this is IN 
                                                    -- and **not** NOT IN
      ) ;

Have you tried moving that conditional check into the 'LEFT JOIN' statement for that table, after the 'ON' clause?

SELECT teams.id
FROM teams
LEFT JOIN user_to_team ON user_to_team.`team_id` = teams.id
AND user_to_team.industry_id NOT IN(1)
WHERE teams.type = 'general'
LIMIT 1

Also, do you have the correct data type for that column?

This assumes that any given team can be associated with more than 1 industry in the user_to_team table.

SELECT DISTINCT teams.team_id
FROM teams
LEFT JOIN user_to_team
  ON user_to_team.team_id = teams.team_id
    AND user_to_team.industry_id = 1
WHERE teams.type = 'general'
  AND user_to_team.team_id IS NULL;