Неожиданный результат при присоединении к таблице в диапазоне лет

Вы не выбираете 3 строки, вы выбираете все строки, которые включают хотя бы одну из 2016, 2017, 2018, и таких строк ровно две (первые две в вашей таблице).

Если вам нужны отдельные строки для каждого из трех случаев, вы можете написать подзапрос для каждого случая и выполнить для них UNION ALL. Поэтому вместо того, чтобы писать

SELECT id, role_id, COALESCE(points / 3) AS points
FROM point_standards
WHERE 2018 BETWEEN YEAR(start_year) AND YEAR(end_year)
    OR 2017 BETWEEN YEAR(start_year) AND YEAR(end_year)
    OR 2016 BETWEEN YEAR(start_year) AND YEAR(end_year)

ты используешь

SELECT id, role_id, COALESCE(points / 3) AS points
FROM point_standards
WHERE 2018 BETWEEN YEAR(start_year) AND YEAR(end_year)
UNION ALL
SELECT id, role_id, COALESCE(points / 3) AS points
FROM point_standards
WHERE 2017 BETWEEN YEAR(start_year) AND YEAR(end_year)
UNION ALL
SELECT id, role_id, COALESCE(points / 3) AS points
FROM point_standards
WHERE 2016 BETWEEN YEAR(start_year) AND YEAR(end_year)

Если у вас есть таблица, из которой вы берете годы, вы можете вместо этого выполнить соединение с этой таблицей, что немного менее подробно.

Изменить. Весь запрос должен выглядеть следующим образом:

SELECT `users`.first_name,
        ps.points

FROM `users`
    # Join the user roles table
    INNER JOIN `user_roles`
        ON `users`.`role_id` = `user_roles`.`id`

    # Get the criteria for 2018, 2017 and 2016
    LEFT JOIN (
            SELECT id, role_id, COALESCE(points / 3) AS points
            FROM point_standards
            WHERE 2018 BETWEEN YEAR(start_year) AND YEAR(end_year)
            UNION ALL
            SELECT id, role_id, COALESCE(points / 3) AS points
            FROM point_standards
            WHERE 2017 BETWEEN YEAR(start_year) AND YEAR(end_year)
            UNION ALL
            SELECT id, role_id, COALESCE(points / 3) AS points
            FROM point_standards
            WHERE 2016 BETWEEN YEAR(start_year) AND YEAR(end_year)
            ) ps
        ON (user_roles.id = ps.role_id)

WHERE users.id = 123
GROUP BY `users`.`id`, ps.id
ORDER BY `points_internal` DESC

Или немного короче (создав собственный подзапрос years):

SELECT `users`.first_name,
        ps.points

FROM `users`
    # Join the user roles table
    INNER JOIN `user_roles`
        ON `users`.`role_id` = `user_roles`.`id`

    # Get the criteria for 2018, 2017 and 2016
    LEFT JOIN (
            SELECT id, role_id, COALESCE(points / 3) AS points
            FROM point_standards
            JOIN (SELECT 2016 AS y UNION SELECT 2017 AS y UNION SELECT 2018 AS y) years
            ON years.y BETWEEN YEAR(start_year) AND YEAR(end_year)
            ) ps
        ON (user_roles.id = ps.role_id)

WHERE users.id = 123
GROUP BY `users`.`id`, ps.id
ORDER BY `points_internal` DESC