StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSorted result differs with inner and left joins in mysql
text
Body
copied!<p>I just found this irregularitry in a query i made, why does the result differ when using inner and left joins? for one, the result is ordered both in the temporary hit table and in the final query?</p> <p>I made a little example to show the problem:</p> <pre><code># cleanup drop temporary table if exists ids; drop temporary table if exists arts; # create temporary tables create temporary table arts ( id int, title varchar(100), posted datetime ); create temporary table ids ( id int, artid int ); # insert dummy articles insert into arts ( id, title, posted ) VALUES ( 1, 'a', '2010-04-01' ); insert into arts ( id, title, posted ) VALUES ( 2, 'b', '2010-07-01' ); insert into arts ( id, title, posted ) VALUES ( 3, 'c', '2010-06-01' ); insert into arts ( id, title, posted ) VALUES ( 4, 'd', '2010-08-01' ); # insert ordered list of hits insert into ids ( id, artid ) values ( 1, 4 ); insert into ids ( id, artid ) values ( 2, 2 ); insert into ids ( id, artid ) values ( 3, 3 ); insert into ids ( id, artid ) values ( 4, 1 ); # execute queries select i.artid, a.posted from ids i left join arts a on a.id = i.artid; select i.artid, a.posted from ids i inner join arts a on a.id = i.artid; # cleanup drop temporary table if exists arts; drop temporary table if exists ids; </code></pre> <p>the first query returns: </p> <pre><code>4,2,3,1 (as expected, ordered by posted-column descending) </code></pre> <p>the second one returns:</p> <pre><code>1,2,3,4 (ordered by pk?) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload