sql range operator

Question

Is there a SQL construct or a trick to do something like:

SELECT i WHERE i BETWEEN 0 AND 10;

?

My first idea is to create a temporary table such as:

CREATE TABLE _range ( i INT PRIMARY KEY );

and fill it

INSERT INTO _range VALUES 0;
INSERT INTO _range VALUES 1;

etc.

Is there a better way?

UPDATE:

I use sqlite in this particular case but i am interested in general answer.

Answer 1

What DB are you using? The between operator generally is legal syntax for SQL. We use it all the time for date ranges.

from http://www.w3schools.com/Sql/sql_between.asp

SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

Answer 2

You did not specify the dbms you use, but with Oracle you could use CONNECT BY:

SELECT ROWNUM-1 i
FROM dual
CONNECT BY ROWNUM <= 10 + 1

Answer 3

Very interesting question. Here's an ugly hack that probably is useless unless your ranges are really that small...

select * from (
  select 1 as num  UNION 
  select 2 as num  UNION 
  select 3 as num  UNION 
  select 4 as num  UNION 
  select 5 as num  UNION 
  select 6 as num  UNION 
  select 7 as num  UNION 
  select 8 as num  UNION 
  select 9 as num  UNION 
  select 10 as num
  ) t ;

+-----+
| num |
+-----+
|   1 | 
|   2 | 
  .... 
|   9 | 
|  10 | 
+-----+
10 rows in set (0.00 sec)

Edit: Ok, so I got to thinking why not use cross joins. So, here is another hack and this one will quickly net you pretty large in memory tables and is perhaps reasonably good.

select POW(2,0)*t0.num + POW(2,1)*t1.num + POW(2,2)*t2.num + POW(2,3)*t3.num  
as num 
from (
  select 0 as num  UNION 
  select 1 as num  
  ) t0, (
  select 0 as num  UNION 
  select 1 as num  
  ) t1, (
  select 0 as num  UNION 
  select 1 as num  
  ) t2, (
  select 0 as num  UNION 
  select 1 as num  
  ) t3 
order by num ;
+------+
| num  |
+------+
|    0 | 
|    1 | 
  ....
|   14 | 
|   15 | 
+------+
16 rows in set (0.00 sec)

Will easily go to any power of 2 and should be fast enough.

Answer 4

An SQL syntax that multiply records is CROSS JOIN I am not sure from your request and comments, but I guess you are trying to do that.

I have done a similar thing to populate a table with a datestamp as string "YYYYMM" for a period of 20 years.