I simply found this irregularitry inside a query i made, how come the end result differ when utilizing inner and left joins? for just one, it makes sense purchased in the temporary hit table as well as in the ultimate query?

I designed a little example to exhibit the issue:

# 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;

the very first query returns:

4,2,3,1 (as expected, ordered by posted-column descending)

the 2nd one returns:

1,2,3,4 (ordered by pk?)

This is exactly what you realized the chosen i.ids within the first query are 1, 2, 3 (and 4 later, most probably) plus they get d, c and b for the reason that order. The chosen i.ids within the second table are 2, 3, 4 matching b, c along with a.

The where condition picks out three randomly selected rows in the join, and it is applied prior to the order with this is most probably what results in the confusion.