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;
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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) );
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) );
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;
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;
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!
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