CODEWITHSUNDEEP
mysqlarrays
musicman
musicman

Reputation: 525

mysql: check if values of an array exist in a DB table

I have an PHP array of numbers (0,1,2,3,4,5) and I want to check which of them are not in my DB table.

How can I do something like:

SELECT num FROM (0,1,2,3,4,5) AS num WHERE num NOT IN (SELECT id FROM sometable);

I'm asking about the right SQL synthax.

Upvotes: 0

Views: 1400

Answers (1)

slavik
slavik

Reputation: 1303

create table sometable (num int not null);
insert into sometable values (1),(1),(4);

Solution:

create temporary table tmp (num int not null);
insert into tmp values (0),(1),(2),(3),(4),(5);
select t.num from tmp t left join sometable s on t.num=s.num where s.num is null;

Or

select t.num from tmp t where t.num not in (select num from sometable);

Out:

+-----+
| num |
+-----+
|   2 |
|   3 |
|   5 |
+-----+

Upvotes: 1

Related Questions