sql range operator

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.

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

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.

with MS SQL you could use CTE :

;with numbs as
(
    select 1 as col 
    union all
    select col + 1 from numbs
    where col < 10
)

select * from numbs

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.

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