MySQL Inner Join changes the order of records

Question

I have a table Table1 which has 5 columns like this

|   ID   |   Name  |   V1   |   V2   |   V3   |
|   1    |    A    |   103  |   507  |   603  |
|   2    |    B    |   514  |   415  |   117  |

and another table Table2 which has values like this

|  Values |  Rooms  |
|   103   |   ABC   |
|   507   |   DEF   |
|   603   |   GHI   |
|   514   |   JKL   |
|   415   |   MNO   |
|   117   |   PQR   |

I am running a join query to get rooms from Table2 joined by Table1 as

SELECT t2.values, t2.rooms, t1.Name FROM Table2 t2
INNER JOIN Table1 t1 ON t1.V1 = t2.Values
                    OR t1.V2 = t2.Values
                    OR t1.V3 = t2.Values;

this query gets the result but in ascending order of t2.values. I do not want to change any order. I just want to get result in whatever the Table1 has values.

|  Values |  Rooms  |  Names  |
|   103   |   ABC   |    A    |
|   117   |   PQR   |    B    |
|   415   |   MNO   |    B    |
|   507   |   DEF   |    A    |
|   514   |   JKL   |    B    |
|   603   |   GHI   |    A    |

The above result is ordered according to T2.Values and these values come form t1.V1, t1.V2, T1.V3. I do not want the order result. I want the result to be according the t1.V1, t1.V2, T1.V3 values. If we see at Table1 the values would be 103, 507, 603, 514, 415, 117 and therefore the result should be

|  Values |  Rooms  |  Names  |
|   103   |   ABC   |    A    |
|   507   |   DEF   |    A    |
|   603   |   GHI   |    A    |
|   415   |   MNO   |    B    |
|   514   |   JKL   |    B    |
|   117   |   PQR   |    B    |

I hope I made my explaination somehow better. Please If it still doesnt clear let me allow to edit it more.

As paxdiablo suggested, I tried adding ORDER BY t1.name but that is not sorting and result is same. Why?

Answer 1

I know you've already accepted an answer, but it looks to me like you want them sorted by the order of ID in table1, and then order of the column (v1, v2, v3) that you've matched on. In which case, something like this should work:

SELECT t2.`values`, t2.rooms, t1.Name FROM Table2 t2
INNER JOIN Table1 t1 ON t1.V1 = t2.`values`
                    OR t1.V2 = t2.`values`
                    OR t1.V3 = t2.`values`
ORDER BY
    t1.id,
    CASE 
        WHEN t1.v1 = t2.`values` THEN 1
        WHEN t1.v2 = t2.`values` THEN 2
        WHEN t1.V3 = t2.`values` THEN 3
    END

(Note I'm quoting values because it's a keyword in SQL...)

What I'm doing here is:

First, I'm ordering by t1.id, which gets you the rough sort order based on the rows in the t1 tables.

Then I'm adding a secondary sort based on which Values column was matched in the result row, using a CASE statement. For each row of your query results, if the result was produced by a match between t1.v1 and t2.values, then the CASE statement evaluates to 1. If the result was because of a match between t1.v2 and t2.values, then we get 2. If the result was because of a match between t1.v3 and t2.values, then we get 3.

So the overall sort order is based first on the order of the rows in t1, and then within that on the order of which column got matched between t1 and t2 for each row in your results, which seems to be the requirement (though it's hard to put into words!)

Answer 2

I just want to get result in whatever the Table1 has values.

This is where you've made your mistake. Table1, at least as far as SQL is concerned, doesn't have an order. Tables are unordered sets to which you impose order when extracting the data (if you wish).

SQL select statements make absolutely no guarantee on the order in which results are returned, unless you specifically use order by or group by. Even select * from table1 can return the rows in whatever order the DBMS sees fit to give them to you.

If you want a specific ordering, you need to ask for it explicitly. For example, if you want them ordered by the room name, whack an order by t1.name at the end of your query. Though I'd probably go the whole hog and use a secondary sort order as well, with order by t1.name, t2.rooms.

Or, to sort on the values, add order by t2.values.

---

For example, punching this schema/data into SQLFiddle:

create table table1(
    id    integer,
    name  varchar(10),
    v1    integer,
    v2    integer,
    v3    integer);
insert into table1 (id,name,v1,v2,v3) values (1,'a',103,507,603);
insert into table1 (id,name,v1,v2,v3) values (2,'b',514,415,117);

create table table2 (
    val   integer,
    room  varchar(10));
insert into table2(val,room) values (103,'abc');
insert into table2(val,room) values (507,'def');
insert into table2(val,room) values (603,'ghi');
insert into table2(val,room) values (514,'jkl');
insert into table2(val,room) values (415,'mno');
insert into table2(val,room) values (117,'pqr');

and then executing:

select t2.val, t2.room, t1.name from table2 t2
inner join table1 t1 on t1.v1 = t2.val
                     or t1.v2 = t2.val
                     or t1.v3 = t2.val

gives us an arbitrary ordering (it may look likes it's ordering by rooms within name but that's not guaranteed):

| val | room | name |
|-----|------|------|
| 103 |  abc |    a |
| 507 |  def |    a |
| 603 |  ghi |    a |
| 514 |  jkl |    b |
| 415 |  mno |    b |
| 117 |  pqr |    b |

When we change that to sort on two descending keys order by t1.name desc, t2.room desc, we can see it re-orders based on that:

| val | room | name |
|-----|------|------|
| 117 |  pqr |    b |
| 415 |  mno |    b |
| 514 |  jkl |    b |
| 603 |  ghi |    a |
| 507 |  def |    a |
| 103 |  abc |    a |

And, finally, changing the ordering clause to order by t2.val asc, we get it in value order:

| val | room | name |
|-----|------|------|
| 103 |  abc |    a |
| 117 |  pqr |    b |
| 415 |  mno |    b |
| 507 |  def |    a |
| 514 |  jkl |    b |
| 603 |  ghi |    a |

---

Finally, if your intent is to order it by the order of columns in each row of table1 (so the order is left to right v1, v2, v3, you can introduce an artificial sort key, either by using a case statement to select based on which column matched, or by running multiple queries which may be more efficient since:

• you're not executing per-row functions, which tend not to scale very well; and
• in larger DBMS', they can be parallelised.

The multiple query option would go something like:

select 1 as minor, t2.val as val, t2.room as room, t1.name as name from table2 t2
    inner join table1 t1 on t1.v1 = t2.val
union all select 2 as minor, t2.val as val, t2.room as room, t1.name as name from table2 t2
    inner join table1 t1 on t1.v2 = t2.val
union all select 3 as minor, t2.val as val, t2.room as room, t1.name as name from table2 t2
    inner join table1 t1 on t1.v3 = t2.val
order by name, minor

and generates:

| minor | val | room | name |
|-------|-----|------|------|
|     1 | 103 |  abc |    a |
|     2 | 507 |  def |    a |
|     3 | 603 |  ghi |    a |
|     1 | 514 |  jkl |    b |
|     2 | 415 |  mno |    b |
|     3 | 117 |  pqr |    b |

You can see there that it uses name as the primary key and the position of the value in the row as the minor key.

Now some people may think it an ugly approach to introduce a fake column for sorting but it's a tried and tested method for increasing performance. However, you shouldn't trust me (or anyone) on that. My primary mantra for optimisation is measure, don't guess.

Answer 3

well the query is sorting the table using Values in ascending order - like "103 < 117 < 415 and so on..." but you want them to take the order in which they are sorted in actual table i.e. "103 than 507 than 603 and so on" which is sorted the way they have been inserted and you just want to retain this order of sorting.. one possible way you can achieve that is using an extra timestamp field in second table that can track the time insertion is done and thus you can use that timestamp like " ORDER BY timestamp " in your query..