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.
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 |
+-----+
