0

Consider table A with columns id (primary key), name and table B with columns id, a_id (foreign key linked with table A id column), address. What will be the sequence of columns if the query is:

SELECT * FROM B INNER JOIN A ON B.a_id = A.id;
0

What do you mean by "sequence of columns"? One interpretation has to do with SELECT * -- the * will list the columns in definition order, first from B, then from A.

It is generally a bad idea to use SELECT *, at least for production queries.

0

The best way that you can do this is to test!

In order to answer your question, I did the following (see fiddle here):

CREATE TABLE a 
(
  b INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY, 
  c VARCHAR (50)
);

populate it:

INSERT INTO a (c) VALUES 
('a1_sdfs'), ('a2_eerre3'), ('a3_sfssdww'), ('a4_sfs'), ('a5_3433443');
SELECT * FROM a;

And then we create and populate the child table:

CREATE TABLE x 
(
  y INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY, 
  a_id INTEGER NOT NULL,
  z VARCHAR (50),
  CONSTRAINT x_a_fk FOREIGN KEY (a_id) REFERENCES a (b)
);

records:

INSERT INTO x (a_id, z) VALUES
(1, '1_1adad'), (1, '2_1adad'), (1, '3_1adad'), (1, '4_1adad'), (1, '5_1adad'), (1, '6_1adad'), 
(2, '1_2adad'), (2, '2_2adad'), (2, '3_2adad'), (2, '4_2adad'), (2, '5_2adad'), (2, '6_2adad'), 
(3, '1_3adad'), (3, '2_3adad'), (3, '3_3adad'), (3, '4_3adad'), (3, '5_3adad'), (3, '6_3adad'), 
(4, '1_4adad'), (4, '2_4adad'), (4, '3_4adad'), (4, '4_4adad'), (4, '5_4adad'), (4, '6_4adad'), 
(5, '1_5adad'), (5, '2_5adad'), (5, '3_5adad'), (5, '4_5adad'), (5, '5_5adad'), (5, '6_5adad');

So, now we test!

First, we run your query:

SELECT * FROM x -- x is the child table - as per the question!
INNER JOIN a 
  ON x.a_id = a.b;

Result:

y   a_id    z   b   c
1   1   1_1adad 1   a1_sdfs
2   1   2_1adad 1   a1_sdfs
3   1   3_1adad 1   a1_sdfs
4   1   4_1adad 1   a1_sdfs
5   1   5_1adad 1   a1_sdfs
...
... snipped for brevity, see the fiddle for full results!
... 
28  5   4_5adad 5   a5_3433443
29  5   5_5adad 5   a5_3433443
30  5   6_5adad 5   a5_3433443

Which is both tables, which appears (perhaps not usual for MySQL) to be logical, i.e. the fields of table x before those of table a. You get the results back in the order specified in tables in the query. If we reverse the order of the SELECT we have:

SELECT * 
FROM a 
INNER JOIN x 
  ON a.b = x.a_id;

Result:

b   c   y   a_id    z
1   a1_sdfs 1   1   1_1adad
1   a1_sdfs 2   1   2_1adad
1   a1_sdfs 3   1   3_1adad
..
.. results agains snipped for brevity - see fiddle
.. 
5   a5_3433443  28  5   4_5adad
5   a5_3433443  29  5   5_5adad
5   a5_3433443  30  5   6_5adad  

This time the results are in the column order of the a table followed by the column order of the x table - again logical (at least to my mind)!

This has a relationship with this post and my explanation of MySQL's behaviour! The sorting of MySQL query results is (apparently) done on the basis of (clustered) PRIMARY KEYs - which is also logical (and happens in fiddle also!). However, in the link above, I quote Bill Karwin who says that:

Without a clear ORDER BY, current versions of InnoDB return rows in the order of the index it reads from. Which index varies, but it always reads from some index. Even reading from the "table" is really an index—it's the primary key index.

As in the comments above, there's no guarantee this will remain the same in the next version of InnoDB. You should treat it as a coincidental behavior, it is not documented and the makers of MySQL don't promise not to change it.

Now, on foot of your question, I did a search for predicates such as "SELECT * JOIN column order" and similar - I didn't get anything significant.

It seems to me that you should NOT rely on MySQL returning the columns in a given order - you should specify your desired column order in the SQL query itself! Apparently, this isn't documented, so a behaviour you come to rely on today, may change without warning or notice in the future - and since it's undocumented, you can't plead any a priori justification for relying on a given column order!

It is best practice to specify column order in the query itself. Then, there is NO possible ambiguity. If someone inserts a new column between your (non-specified) columns, you may have new fields that your (if you're using one) framework might misinterpret?

Make life easier for yourself and all of your colleagues who may/will come after you, specify the columns in the order you require. It's only a bit of one-off extra typing and will avoid potential, difficult-to-find bugs in the future!

1
  • The order of columns in the output of query is very well defined in the standard and MySQL follows it. Details here: join. The number of columns and their order differs between JOIN .. ON, JOIN .. USING and NATURAL JOIN. – ypercubeᵀᴹ Jul 20 '20 at 1:36
0

tables are unsorted by nature

so also the joined tables are unsorted

order them if you need it like you want.

So the columns are more complicated and more nested they become more complicated it gets.

Here are all possibilities with two tables

the result differs, if you use ON Clause or the USING clause

In the USING clause, only RIGHT JOIN gives you another result

CREATE TABLE t1 (i INT, j INT);
CREATE TABLE t2 (k INT, j INT);
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t2 VALUES(1, 1);
SELECT * FROM t1 NATURAL JOIN t2;
 j |  i |  k
-: | -: | -:
 1 |  1 |  1
SELECT * FROM t1 INNER JOIN t2 USING (j);
 j |  i |  k
-: | -: | -:
 1 |  1 |  1
SELECT * FROM t1 JOIN t2 USING (j);
 j |  i |  k
-: | -: | -:
 1 |  1 |  1
SELECT * FROM t1 LEFT JOIN t2 USING (j);
 j |  i |  k
-: | -: | -:
 1 |  1 |  1
SELECT * FROM t1 RiGHT JOIN t2 USING (j);
 j |  k |  i
-: | -: | -:
 1 |  1 |  1
SELECT * FROM t1 LEFT JOIN t2 ON (t1.j = t2.j);
 i |  j |  k |  j
-: | -: | -: | -:
 1 |  1 |  1 |  1
SELECT * FROM t1 RIGHT JOIN t2 ON (t1.j = t2.j);
 i |  j |  k |  j
-: | -: | -: | -:
 1 |  1 |  1 |  1
SELECT * FROM t1 INNER JOIN t2 ON (t1.j = t2.j);
 i |  j |  k |  j
-: | -: | -: | -:
 1 |  1 |  1 |  1

db<>fiddle here

2
  • That is, ORDER BY is the only way to control the ordering of rows. – Rick James Jul 19 '20 at 18:54
  • The question is about order of columns, not of rows. – ypercubeᵀᴹ Jul 20 '20 at 1:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.