MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;


@@version


8.0.12


CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `user` (`id`, `name`) VALUES
(1, 'user1'),
(2, 'user2'),
(3, 'user3'),
(4, 'user4');
CREATE TABLE `user_favorites_user` (
  `source_user_id` int(11) NOT NULL,
  `favorited_user_id` int(11) NOT NULL,
  KEY `source_user_id` (`source_user_id`),
  KEY `favorited_user_id` (`favorited_user_id`),
  CONSTRAINT `user_favorites_user_ibfk_1` FOREIGN KEY (`source_user_id`) REFERENCES `user` (`id`),
  CONSTRAINT `user_favorites_user_ibfk_2` FOREIGN KEY (`favorited_user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `user_favorites_user` (`source_user_id`, `favorited_user_id`) VALUES
(1, 3),
(1, 4);
-- bug: is_favourite is null with order by 
select user.*, favorites.is_favorited, 
coalesce(favorites.is_favorited,0) as is_favorited_coalesced from user
left join (
select 1 as is_favorited, favorited_user_id from user_favorites_user
where source_user_id = '1'
) favorites on favorites.favorited_user_id = user.id
order by is_favorited_coalesced asc


id
name
is_favorited
is_favorited_coalesced


1
user1
null
0

2
user2
null
0

3
user3
null
1

4
user4
null
1


-- works if without order by 
select user.*, favorites.is_favorited, 
coalesce(favorites.is_favorited,0) as is_favorited_coalesced from user
left join (
select 1 as is_favorited, favorited_user_id from user_favorites_user
where source_user_id = '1'
) favorites on favorites.favorited_user_id = user.id


id
name
is_favorited
is_favorited_coalesced


3
user3
1
1

4
user4
1
1

1
user1
null
0

2
user2
null
0


-- materialization
select user.*, favorites.is_favorited, 
coalesce(favorites.is_favorited,0) as is_favorited_coalesced from user
left join (
select 1 as is_favorited, favorited_user_id from user_favorites_user
where source_user_id = '1' limit 1000000
) favorites on favorites.favorited_user_id = user.id
order by is_favorited_coalesced desc


id
name
is_favorited
is_favorited_coalesced


3
user3
1
1

4
user4
1
1

1
user1
null
0

2
user2
null
0