SQL - getting the rows that aren't there

Question

Title might sound strange but I'll try to explain the problem as easy as possible. Let's start with example. I got a table with 2 columns - id, ip. Let's say I got 3 rows with id 2,3,5. Now, I need to get any row that isn't between id 1 AND 5, which is clearly 1 and 4. Currently I'm stuck with this query:

SELECT * 
FROM `votes` 
WHERE ip = "1.1.1.1."  
AND question_id BETWEEN 1 AND 5

Answer 1

sounds bizarre but this is what many do.

create a helper table. Use it for left joins

create table amfn
(   -- All My Favorite Numbers  
    id int auto_increment primary key,
    theWhat char(1) null
)engine=MyIsam;   --  <----- somewhat important

insert amfn(theWhat) values (null),(null),(null),(null),(null),(null),(null),(null),(null),(null); -- 10
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;

select count(*),min(id),max(id) from amfn;
+----------+---------+---------+
| count(*) | min(id) | max(id) |
+----------+---------+---------+
|  1310720 |       1 | 1310720 |
+----------+---------+---------+
1 row in set (0.00 sec)

Your Schema:

create table votes
(   question_id int not null,
    ip varchar(20) not null
);
insert votes (question_id,ip) values (1,'xxxx'),(2,'1.1.1.1'),(3,'1.1.1.1'),(4,'1.6.1.1'),(5,'1.1.1.1');

The Query:

select a.id,v.question_id,v.ip
from amfn a
left join votes v
on v.question_id=a.id and v.ip='1.1.1.1'
where a.id between 1 and 5 and v.question_id is null;
+----+-------------+------+
| id | question_id | ip   |
+----+-------------+------+
|  1 |        NULL | NULL |
|  4 |        NULL | NULL |
+----+-------------+------+
2 rows in set (0.00 sec)     <------------- boy that is fast

Edit (to show Conrad Frix the time differences).

My above approach to create 5242880 rows, 23.5 seconds. Conrad's approach, 168.5 seconds. I will stick with my approach :>

Answer 2

A really unusual request;

But you may get what you want by creating an auxiliary sequence of 1..5, then select all the ids from this sequence where there's no matching entry in the votes table.

SELECT id 
FROM (
  SELECT 1 AS id UNION ALL 
  SELECT 2 UNION ALL 
  SELECT 3 UNION ALL 
  SELECT 4 UNION ALL 
  SELECT 5 UNION ALL 
) AS temp 
WHERE temp.id NOT IN (SELECT DISTINCT question_id FROM votes);