Mysql get values missing in the In Clause

Question

I want to get values missing in the in clause of a query

people

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

Place

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

i want to find places where there are no people , when i give a list of locations in the in clause of the Query

i don't want to find all the locations which are not in the people table, only those ,in which i provide in the in clause

Query

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

OUTPUT expected

|Location_id|
           6|
           9|

Raging Bull · Accepted Answer · 2014-05-03T06:39:11.693

1

Using a JOIN instead of two IN operations (improved performance).

Try this:

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);

Result:

LOCATION_ID
6
9

See result in SQL Fiddle.

EDIT:

Without using 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);

edited May 03 '14 at 06:39

answered May 03 '14 at 05:46

Raging Bull

18,593
13
50
55

The select distinct location_id from people, is that neccesary , as it does full table scan – Naveen Kumar May 03 '14 at 05:54
@NaveenKumar: Updated my answer since a `join` is better than two `IN`s. – Raging Bull May 03 '14 at 05:56
Can we do it with without joining with the place table – Naveen Kumar May 03 '14 at 06:28
@NaveenKumar: Ofcourse, by using two `IN` operations. But I won't recommend that, since using `JOIN` is much faster than that. – Raging Bull May 03 '14 at 06:32
@NaveenKumar: See the edit in my answer. – Raging Bull May 03 '14 at 06:39
How do you do this on the same table? – arjun Apr 08 '21 at 11:13
@arjun, can you provide more information? have you asked a question on SO? – Raging Bull Apr 17 '21 at 10:16

Ilesh Patel · Answer 2 · 2014-05-03T06:06:31.990

0

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

edited May 03 '14 at 06:06

answered May 03 '14 at 05:46

Ilesh Patel

2,053
16
27

score 0 · Answer 3 · answered May 03 '14 at 05:46

0

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 is use to join two or more table .

answered May 03 '14 at 05:46

Gopal Joshi

2,350
22
49

score 0 · Answer 4 · answered May 03 '14 at 05:52

0

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);

answered May 03 '14 at 05:52

Surendheran

187
1
18

score 0 · Answer 5 · answered May 03 '14 at 05:52

0

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

answered May 03 '14 at 05:52

mehere

1,487
5
28
50

Mysql get values missing in the In Clause

5 Answers5