Is there a way to multiply results in SQL?

Question

I am building a website which populates from a database. I'm testing now, and I'd like to see what my site will look like with a lot of data (mainly so I can watch performance, build out pagination, and address any issues with presentation). I have about 10 pieces of data in my table, which is great, but I'd like to display about 2,000 on my page.

Is there a way I can read from the same SELECT * FROM table statement over and over again in the same query in order to read the table multiple times?

I can do this by feeding all my results into a variable and echoing that variable multiple times, but it won't allow me to set a LIMIT or give me the proper count of rows from the query.

I'm surprised I haven't found a way to do this by Googling. It seems like it would be an easy, built-in thing.

If there's not, can you suggest any other way I can do this without modifying my original table?

Look for "data faker" or "fake data generator" or just write your own in the scripting language of your choice. Inserting test data is super simple, and can even be done from a CSV source using `LOAD DATA INFILE`. — tadman, Oct 24 '19 at 18:00

Sree · Answer 1 · 2019-10-24T20:58:35.470

2

Please use Cross Join. Cross Join will give you a cartesian product of rows from tables joined. Cross Join can generate a lot of data in quick amount of time. Can be useful for extensive testing.

Example: SELECT * FROM A CROSS JOIN B;

You can cross join on the same table as well.

edited Oct 24 '19 at 20:58

answered Oct 24 '19 at 18:13

Sree

21
2

Provide an example to really make this a helpful answer something like: `Select A.* from yourBaseTable A cross join (Select 1 union all select 2 union all select 3 union all select 4 union all`....) will multiply your records by 4 – xQbert Oct 24 '19 at 18:17
Yes, a cross join is a good idea. (Like in `select a.* from mytable a cross join mytable b cross join mytable c` to get n^3 rows.) What you are showing in your answer is not a cross join though, but an obfuscated inner join, as you provide join criteria - only that you moved it from `ON` to `WHERE`. – Thorsten Kettner Oct 24 '19 at 20:33

score 1 · Answer 2 · answered Oct 24 '19 at 20:48

As of MySQL 8 you can use a recursive query to get your rows multifold:

with recursive cte (a, b, c) as
(
  select a, b, 1 from mytable
  union all
  select a, b, c + 1 from cte where c < 10 -- ten times as many
)
select a, b from cte;

(You can of course alter the generated values in the part after union all, e.g.: select a + 5, b * 2, c + 1 from cte where c < 10.)

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3a2699c167e1f4a7ffbe4e9b17ac7241

Is there a way to multiply results in SQL?

2 Answers2