DROP TABLE t1;

DROP TABLE t2;

CREATE TABLE `t1` (

	`t1_seq` INT NOT NULL, 

	`c1` VARCHAR(10) NOT NULL ,

	PRIMARY KEY (`t1_seq`) USING BTREE

) ENGINE=INNODB;

CREATE TABLE `t2` (

	`t2_seq` INT NOT NULL,

	`t1_seq` INT NOT NULL,

	`c2` VARCHAR(10) NOT NULL ,

	PRIMARY KEY (`t2_seq`, `t1_seq`) USING BTREE

) ENGINE=INNODB;

INSERT INTO t1 VALUES(1, 'A');

INSERT INTO t2 VALUES(1, 1, 'T2-1-1');

INSERT INTO t2 VALUES(2, 1, 'T2-1-2');

INSERT INTO t2 VALUES(3, 1, 'T2-1-3');

MariaDB [testdb]> SELECT @@version;

+-----------------+

| @@version       |

+-----------------+

| 10.11.2-MariaDB |

+-----------------+

1 ROW IN SET (0.000 sec)

SELECT LPAD(@rownum := @rownum + 1, 8, 0) AS str_num

     , t1.t1_seq

     , t2.t2_seq

     , t1.c1

     , t2.c2

  FROM t1 

       INNER JOIN t2 ON (t1.t1_seq = t2.t1_seq)

       CROSS JOIN ( SELECT @rownum := 0 ) X

; 

+------+-------------+------------+--------+---------------+------+---------+------+------+-------------------------------------------------+

| id   | select_type | TABLE      | TYPE   | possible_keys | KEY  | key_len | ref  | ROWS | Extra                                           |

+------+-------------+------------+--------+---------------+------+---------+------+------+-------------------------------------------------+

|    1 | PRIMARY     | <derived2> | system | NULL          | NULL | NULL    | NULL | 1    |                                                 |

|    1 | PRIMARY     | t1         | ALL    | PRIMARY       | NULL | NULL    | NULL | 1    |                                                 |

|    1 | PRIMARY     | t2         | ALL    | NULL          | NULL | NULL    | NULL | 3    | USING WHERE; USING JOIN buffer (flat, BNL JOIN) |

|    2 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL | NO TABLES used                                  |

+------+-------------+------------+--------+---------------+------+---------+------+------+-------------------------------------------------+

4 ROWS IN SET (0.000 sec)

+----------+--------+--------+----+--------+

| str_num  | t1_seq | t2_seq | c1 | c2     |

+----------+--------+--------+----+--------+

| 00000001 |      1 |      1 | A  | T2-1-1 |

| 00000002 |      1 |      2 | A  | T2-1-2 |

| 00000003 |      1 |      3 | A  | T2-1-3 |

+----------+--------+--------+----+--------+

3 ROWS IN SET (0.002 sec)

SELECT STRAIGHT_JOIN LPAD(@rownum := @rownum + 1, 8, 0) AS str_num

     , t1.t1_seq

     , t2.t2_seq

     , t1.c1

     , t2.c2

  FROM t1 

       INNER JOIN t2 ON (t1.t1_seq = t2.t1_seq)

       CROSS JOIN ( SELECT @rownum := 0 ) X

; 

+------+-------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+

| id   | select_type | TABLE      | TYPE | possible_keys | KEY  | key_len | ref  | ROWS | Extra                                           |

+------+-------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+

|    1 | PRIMARY     | t1         | ALL  | PRIMARY       | NULL | NULL    | NULL | 1    |                                                 |

|    1 | PRIMARY     | t2         | ALL  | NULL          | NULL | NULL    | NULL | 3    | USING WHERE; USING JOIN buffer (flat, BNL JOIN) |

|    1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL | 1    | USING JOIN buffer (incremental, BNL JOIN)       |

|    2 | DERIVED     | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NO TABLES used                                  |

+------+-------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+

4 ROWS IN SET (0.000 sec)

MariaDB [lololdb]> 

+----------+--------+--------+----+--------+

| str_num  | t1_seq | t2_seq | c1 | c2     |

+----------+--------+--------+----+--------+

| 00000001 |      1 |      1 | A  | T2-1-1 |

| 00000002 |      1 |      2 | A  | T2-1-2 |

| 00000003 |      1 |      3 | A  | T2-1-3 |

| 00000004 |      1 |      1 | A  | T2-1-1 |

| 00000005 |      1 |      2 | A  | T2-1-2 |

| 00000006 |      1 |      3 | A  | T2-1-3 |

+----------+--------+--------+----+--------+

6 ROWS IN SET (0.001 sec)

MariaDB [testdb]>