SQL INNER JOIN mechanism

Question

Just wondering how INNER JOIN works internally. I have two test tables:

test1: id, field test2: id, field

test1: (123, a) (123, b) test2: (123, c) (123, d)

SELECT *
FROM test1 INNER JOIN
     test2
     ON test1.id = test2.id;

The result was:

a c
b c
a d
b d

But I was expecting:

a c
a d
b c
b d

I thought the query would select a record from left table, loop through right table for all matched record before move to the next record. Apparently it wasn't the case. Could anyone give me a quick tutorial on this one? Thx

Answer 1

SQL tables -- and result sets -- represent unordered sets. So, the two versions of your result set are the same, because they differ only in ordering.

If you want results in a particular order, you need to use order by. Please remember that!