CROSS JOIN doesn't work properly

Question

anyone knows how its possible that queries: SELECT a.id, b.id FROM a CROSS JOIN b and SELECT a.id, b.id FROM b CROSS JOIN a return the same result? In both cases records from less numerous table are assigned to more numerous table. I want to get something like this:

`| a.id | b.id |
 -------+-----+
    1   |  q  |
    1   |  w  |
    1   |  e  |
    1   |  r  |
    2   |  q  |
    2   |  w  |
    2   |  e  |
    2   |  r  |

`

but im getting result like this:

`| a.id | b.id |
 -------+-----+
    1   |  q  |
    2   |  q  |
    1   |  w  |
    2   |  w  |
    1   |  e  |
    2   |  e  |
    1   |  r  |
    2   |  r  |

`

It's kinda strange that mysql automatically choose order of cross joined tabled depending of their numerous. I know i can use ORDER BY but i need to do this by CROSS JOIN.

There is more complex problem, i want to get 10 records per a.id. I saw solution for that: row counting with IF condition in SELECT clause. That row counting require rows sorted by a.id in raw result (without order by). Is there any other solution to do that?

Answer 1

First, the two results that you show are the same. With no order by clause, SQL results sets, like SQL tables, represent unordered sets. The ordering is immaterial. So, the sets are the same.

Your problem is quite different from this. If you want ten rows from table b for each record in table a, then you need to enumerate them. Typically, the fastest way in MySQL is to use a subquery and variables:

select a.*, b.*
from a left join
     (select b.*,
             (@rn := if(@a = b.aid, @rn + 1,
                        if(@a := b.aid, 1, 1)
                       )
             ) as seqnum
      from b cross join
           (select @rn := 0, @a := 0) params
      order by b.aid
     ) b
where seqnum <= 10
order by a.aid;

There are other solutions, but this is undoubtedly the best.

Answer 2

NO, without a ORDER BY there is no specific order guaranteed. if you want a specific order to be maintained always then use order by clause. So in your case do like

SELECT a.id, b.id FROM a CROSS JOIN b
ORDER BY a.id;

i want to get 10 records per a.id.

Use a LIMIT clause along with ORDER BY like below; but without using ORDER BY you can never assure any order. Check MySQL documentation for more information.

SELECT a.id, b.id FROM a CROSS JOIN b
ORDER BY a.id
LIMIT 0,10;