Mysql获取in子句中缺失的值

Question

我想获得在查询的in子句中缺失的值

的人

ID    |  Name | location_id | 
1     |    Rex|          5  |
2     |    Tex|          7  |
3     |    Jim|          8  |
4     |   John|          11 |
5     |    Tom|          12 |

地方

location_id | Place    |
5           | New York |
6           | London   |
7           | Madrid   |
8           | Paris    |   
9           | Dublin   |   
10          | Chicago  |   
11          | Berlin   |   
12          | Colombo  |   

我想找到没有人的地方，当我在查询的in子句中给出位置列表

我不想找到所有不在people表中的位置，只找到我在in子句

中提供的位置。查询

SELECT location_id FROM people WHERE location_id in (5,6,7,8,9);

期望输出

|Location_id|
           6|
           9|

Answer 1

使用一个JOIN而不是两个IN操作(提高性能)。

试试这个:

SELECT pl.location_id
FROM place pl LEFT JOIN
     people pp ON pl.location_id=pp.location_id
WHERE pp.location_id IS NULL 
      AND pl.location_id IN (5,6,7,8,9);

结果:

LOCATION_ID
6
9

参见SQL Fiddle。

编辑:

不使用JOIN:

SELECT location_id
FROM place
WHERE location_id NOT IN 
          (SELECT location_id FROM people)
AND pl.location_id IN (5,6,7,8,9);

Answer 2

Try Below Query

    select place from place 
          inner join poeple on place.location_id <> people.location_id where AND 
    place.location_id IN (5,6,7,8,9);

Inner join用于连接两个或多个表。

Answer 3

select p.location_id FROM people p join Place l on 
p.location_id!=l.location_id WHERE p.location_id in (5,6,7,8,9);

Answer 4

select l.location_id from people p left join place l on p.location_id=l。where p. Location_id not in (5,6,7,8,9)

Answer 5

SELECT location_id FROM place WHERE location_id NOT IN (SELECT location_id  FROM people) AND location_id IN (5,6,7,8,9);