CODEWITHSUNDEEP
mysql
David Pet
David Pet

Reputation: 107

MySql Query results in a specific way

I have two tables, my_table1 and my_table2.

my_table1 contains numbers from 1 to 10 and my_table2 contains letters a, b, c and d.

I want to do a query which returns the following:

1 a
1 b
1 c
1 d
2 a
2 b
2 c
2 d

All the way until the end.

Is there any possible way to do this in SQL?

Thanks in advance.

Upvotes: 0

Views: 29

Answers (2)

GolezTrol
GolezTrol

Reputation: 116110

That is a cross join. You can write that in the simple (old) form by just selecting select * from table1, table2, but this is outdated syntax, and your queries will become very hard to read if you mix this syntax with the more modern explicit joins that were introduced in 1992. So, I'd chose to write the explicit cross join.

Also, it looks like you want the results sorted. If you're lucky this happens automatically, but you cannot be sure that this will always happen, so best to specify it if you need it. If not, omit the order by clause, because it does make the query slower.

select
  n.nr,
  l.letter
from
  my_table1 n
  cross join my_table2 l
order by
  n.nr,
  l.letter

Upvotes: 1

Marvin
Marvin

Reputation: 14345

That is a CROSS JOIN, in MySQL equivalent to an INNER JOIN or a regular comma:

SELECT * FROM my_table1, my_table2;

cf. https://dev.mysql.com/doc/refman/5.7/en/join.html

Upvotes: 1

Related Questions