How to generate a sequence in mysql Consider this table in mysql

create table numbers (number int);
insert into numbers values (3), (2), (9);
select * from numbers;

+--------+
| number |
+--------+
|      3 |
|      2 |
|      9 |
+--------+


Is there a simple query to generate a table with the following columns


The numbers from 1 till 10
1 if the number is present in the table numbers, and 0 otherwise


I guess you have to create a sequence of numbers in order to do this. If possible, I want to create such a sequence without storing it in the database.

Related question: is there a select query that generates a sequence of numbers from 1 till 10 (or 100 or 1000)?
IN MariaDB

MariaDB has a SEQUENCE Storage Engine. So, if you are using MariaDB, all your sequence troubles are over (or have just begun).

Sequence of 10 numbers

select * from seq_1_to_10;


NOT USING TABLES

Sequence of 10 numbers

select * from
(select 0 x union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) A;


Sequence of 100 numbers

select (t*10+u+1) x from
(select 0 t union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) A,
(select 0 u union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) B
order by x;


Sequence of 1000 numbers

select (h*100+t*10+u+1) x from
(select 0 h union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) A,
(select 0 t union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) B,
(select 0 u union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) C
order by x;


Sequence of 10000 numbers

select (th*1000+h*100+t*10+u+1) x from
(select 0 th union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) A,
(select 0 h union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) B,
(select 0 t union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) C,
(select 0 u union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) D
order by x;


USING TABLES

Sequence of 10 numbers

use test
drop table if exists seq10;
create table seq10
(x int not null auto_increment primary key);
insert into seq10 values (),(),(),(),(),(),(),(),(),();
select * from seq10;


Sequence of 100 numbers

use test
drop table if exists seq100;
create table seq100
(x int not null auto_increment primary key);
insert into seq100 values (),(),(),(),(),(),(),(),(),();
insert into seq100 values (),(),(),(),(),(),(),(),(),();
insert into seq100 values (),(),(),(),(),(),(),(),(),();
insert into seq100 values (),(),(),(),(),(),(),(),(),();
insert into seq100 values (),(),(),(),(),(),(),(),(),();
insert into seq100 select x + 50 from seq100;
select * from seq100;


Sequence of 1000 numbers

use test
drop table if exists seq1000;
create table seq1000
(x int not null auto_increment primary key);
insert into seq1000 values ();
set @p= -1;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) &lt;= 1000;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) &lt;= 1000;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) &lt;= 1000;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) &lt;= 1000;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) &lt;= 1000;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) &lt;= 1000;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) &lt;= 1000;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) &lt;= 1000;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) &lt;= 1000;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) &lt;= 1000;
select * from seq1000;


Sequence of whatever numbers (Example: 3.5 million)

use test
drop table if exists seq;
create table seq
(x int not null auto_increment primary key);
insert into seq values ();
set @maxseq = 3500000;
set @p = -1;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) &lt;= @maxseq;
select max(x),count(x) from seq;


YOUR ACTUAL QUESTION

Using the sequence 0..9, you left join the sequence against the table

select A.number,1-ISNULL(B.number) present from
(select 0 number union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) A
left join numbers B using (number);


Your sample data with the new query

mysql&gt; drop table numbers;
Query OK, 0 rows affected (0.01 sec)

mysql&gt; drop table if exists numbers;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql&gt; create table numbers (number int);
Query OK, 0 rows affected (0.02 sec)

mysql&gt; insert into numbers values (3), (2), (9);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql&gt; select A.number,1-ISNULL(B.number) present from
    -&gt; (select 0 number union select 1 union select 2 union select 3 union select 4 union
    -&gt; select 5 union select 6 union select 7 union select 8 union select 9) A
    -&gt; left join numbers B using (number);
+--------+---------+
| number | present |
+--------+---------+
|      0 |       0 |
|      1 |       0 |
|      2 |       1 |
|      3 |       1 |
|      4 |       0 |
|      5 |       0 |
|      6 |       0 |
|      7 |       0 |
|      8 |       0 |
|      9 |       1 |
+--------+---------+
10 rows in set (0.00 sec)

mysql&gt;


GIVE IT A TRY !!!