CODEWITHSUNDEEP
mysqlsqldatabasesubqueryunion
Kozame
Kozame

Reputation: 289

Mysql request if not exist

I need help for a request, maybe it's easy but I didn't succeed to find the way.

I have a table T with a lot of rows. One of column C, contains an id. Next to that, I have a listing L of id :

table T :

- id1
- id2
- id3

listing L which is not a table, just a listing :

id1, id3, id4

I want to find in L, all ids that not in T. Here : id4.

My query:

SELECT id FROM T WHERE id IN (id1, id3, id4)

allow me to find all id of L present in T, but I wan't the contrary : all id of L NOT present in T.

Someone has an idea ?

Upvotes: 1

Views: 98

Answers (2)

The Impaler
The Impaler

Reputation: 48800

In MySQL 8.x you can do: if, for example, the list L has three values (123, 456, 789) then you can do:

with L as (
  select 123 as id
  union select 456
  union select 789 -- add more values to the list L here
)
select id from L where id not in (select id from T)

In MySQL 5.x you can do:

select L.id
from (
  select 123 as id
  union select 456
  union select 789
) L
left join T on L.id = T.id
where T.id is null

Upvotes: 1

GMB
GMB

Reputation: 222482

This could be done with a NOT EXIST condition and a correlated subquery. This query will give you all id of L NOT present in T :

SELECT L.id
FROM L
WHERE NOT EXISTS (SELECT 1 FROM T WHERE L.id = T.id)

If L is a list and not a table, then you could turn it into a table, as follows :

SELECT L.id
FROM (SELECT 'id1' id UNION ALL SELECT 'id3' UNION ALL SELECT 'id4') L
WHERE NOT EXISTS (SELECT 1 FROM T WHERE L.id = T.id)

Upvotes: 3

Related Questions