CODEWITHSUNDEEP
mysql
Abeer Elhout
Abeer Elhout

Reputation: 161

Mysql Different order by result between inner join query and exists query

I have 2 tables in the database

  1. User table has columns (name, name_ar, ...)
  2. User Profile table has columns (user_id, office_id, address, mobile, ...)

the relationship between the two tables is one to one relation

Now, I'm trying to filter users by their office and order them by name_ar. I tried two different queries to do this and I expect the same result from the two queries but the result is different in order.

SELECT
    `id`, `name_ar`
FROM
    `users`
WHERE EXISTS
    (
    SELECT
        *
    FROM
        `user_profiles`
    WHERE
        `users`.`id` = `user_profiles`.`user_id` AND `office_id` = 1
) AND(
    `group` = "doctor" AND `state` = "active"
) AND `users`.`deleted_at` IS NULL
ORDER BY
    `name_ar` IS NULL, `name_ar` ASC

exists query result

SELECT
    `u`.`id`,
    `name_ar`
FROM
    `users` u
INNER JOIN `user_profiles` up ON
    `u`.`id` = `up`.`user_id`
WHERE
    `group` = "doctor" AND `state` = "active" AND `up`.`office_id` = 1
ORDER BY
    `name_ar` IS NULL, `name_ar` ASC

inner join query result

the two results do not have the same order from the beginning of appearing null value in name_ar column (from the fifth row exactly the order is different between the two results), Can any explain for me why is this happens? Is it because of null values or another reason?

Upvotes: 0

Views: 383

Answers (1)

forpas
forpas

Reputation: 164174

The 1st condition of the ORDER BY clause:

`name_ar` IS NULL

sends all nulls to the end of the results.

The 2nd:

`name_ar` ASC

sorts the non null names alphabetically but when it comes to the null names at the end there is not any defined order for them.

What you can do is add another final condition, like:

`id` ASC

so you have all the nulls (and duplicate names if they exist) sorted by id:

ORDER BY `name_ar` IS NULL, `name_ar`, `id`

Upvotes: 1

Related Questions