So this is a bit weird as I've never had to deal with this issue before. I have a dataset with 4 basic joins to find the user who edited, created the record set.

SELECT `tasks`.`id`, `tasks`.`title`, `tasks`.`description`, `tasks`.`created_ts`, `tasks`.`modified_ts`, 
`u1`.`id` AS `assigned_to_id`,
`u1`.`firstname` AS `assigned_to_firstname`, 
`u1`.`surname` AS `assigned_to_surname`,
`u2`.`id` AS `author_id`,
`u2`.`firstname` AS `author_firstname`,
`u2`.`surname` AS `author_surname`,
`u3`.`id` AS `modified_by_id`,
`u3`.`firstname` AS `modified_by_firstname`,
`u3`.`surname` AS `modified_by_surname`,
`t1`.`id`AS `priority_id`,
`t1`.`priority` AS `priority_priority`,
FROM tasks
LEFT OUTER JOIN `users` u1 ON `u1`.`id` = `tasks`.`assigned_user_id`
LEFT JOIN `users` u2 ON  `u2`.`id` = `tasks`.`author_user_id` 
LEFT JOIN `users` u3 ON `u3`.`id` = `tasks`.`modified_by_user_id` 
LEFT JOIN `type_priority` t1 ON `tasks`.`type_priority_id` = `t1`.`id`

when I run this query, I only get the results with a particular user (ID:1). It just skips all other records that have joins with different users. What's funny is, when I don't select the fields from user tables (u1, u2 and u3), I get all the results that I expect.

  • 1
    Well you're limiting it to only 100 results without an ORDER BY clause, so which 100 records do you expect back?...if you don't specify to the SQL engine, then you can essentially expect the results to be 100 random rows.
    – J.D.
    Commented Aug 9, 2022 at 2:15
  • 1
    Please show us both queries, including the WHERE ... = 1.
    – Rick James
    Commented Aug 10, 2022 at 23:16
  • Thanks @RickJames, It was an issue with the ordering of row and because of the limit, it was only fetching the first 100. I added order by and it worked fine. Sorry for the silly confusion. Commented Aug 11, 2022 at 23:15

1 Answer 1


You aren't specifying an order, so the LIMIT picks the first 100 rows in whatever order the query read them.

That order depends on which index was chosen by the optimizer, and the table order. The optimizer tries to choose these based on what it thinks will be the most efficient given the columns and conditions in your query.

So it's possible that omitting some columns from the select-list will result in a different index, and therefore a different set of rows read first.

You should use EXPLAIN to analyze the table order and index choices for your two query forms.

  • Thank you! I've been going crazy trying to figure this out. It was just fetching them out of order. Commented Aug 10, 2022 at 4:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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