Re: Select enumeration of intergers

From: Tonkuma <tonkuma_at_jp.ibm.com>
Date: 17 Jul 2006 07:38:24 -0700
Message-ID: <1153147104.697942.62370@75g2000cwc.googlegroups.com>

According to a "SQL Super Reference" written in Japanese (references 6 DBMS including MySQL),
MySQL support to specify multiple rows in VALUES.You no need to use UNION and extra VIEW.
To be honest, I don't know performance characteristics of MySQL, so I'm not sure how much influence for performance by following example. But, at least SQL statement get simpler.

create view all_integers (value) as
select

       d1.digit + d2.digit*10 + d3.digit*100 + .... + d10.digit*10000000000

  from (VALUES 0,1,2,3,4,5,6,7,8,9) d1(digit)


     , (VALUES 0,1,2,3,4,5,6,7,8,9) d2(digit)

     , (VALUES 0,1,2,3,4,5,6,7,8,9) d3(digit)

......


     , (VALUES 0,1,2,3,4,5,6,7,8,9) d10(digit)

Another idea is without using all_integers view, write only neccesary part in every statement.
For example:
1)
SELECT value
  FROM (select

               d1.digit + d2.digit*10 + d3.digit*100
          from (VALUES 0,1,2,3,4,5,6,7,8,9) d1(digit)
             , (VALUES 0,1,2,3,4,5,6,7,8,9) d2(digit)
             , (VALUES 0,1,2,3,4,5,6,7,8,9) d3(digit)
       ) S (value)

 WHERE value BETWEEN 510 AND 514

or
2)
SELECT value
  FROM (select

               d1.digit + d2.digit*10 + 500
          from (VALUES 0,1,2,3,4,5,6,7,8,9) d1(digit)
             , (VALUES 0,1,2,3,4,5,6,7,8,9) d2(digit)
       ) S (value)

 WHERE value BETWEEN 510 AND 514 Received on Mon Jul 17 2006 - 09:38:24 CDT