MariaDB 10.3

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

CREATE TABLE `cars` (
  `brand` varchar(128) DEFAULT NULL,
  `model` varchar(128) DEFAULT NULL,
  `value` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `used_cars` (
  `brand` varchar(128) DEFAULT NULL,
  `model` varchar(128) DEFAULT NULL,
  `price` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `cars` (`brand`, `model`, `value`) VALUES
('Volvo', 'S40', 1),
('Porsche', 'Carrera', 4),
('Opel', 'Corsa', 3),
('Renault', 'Clio', 1),
('Renault', 'Megane', 3),
('Volvo', 'V40', 2);
INSERT INTO `used_cars` (`brand`, `model`, `price`) VALUES
('Volvo', 'S40', 2500),
('Porsche', 'Carrera', 7500);

SELECT (@row := @row + 1) AS score,
   qry.brand,
   qry.model,
   qry.used_present
FROM
  (SELECT cars.brand,
          cars.model,
          (used_cars.price IS NOT NULL) AS used_present
   FROM cars
   LEFT JOIN used_cars ON used_cars.model = cars.model
                          AND used_cars.brand = cars.brand) AS qry
CROSS JOIN
  (SELECT @row:=0) AS r
ORDER BY qry.used_present DESC,
         qry.brand ASC;

score	brand	model	used_present
2	Porsche	Carrera	1
1	Volvo	S40	1
3	Opel	Corsa	0
5	Renault	Megane	0
4	Renault	Clio	0
6	Volvo	V40	0

version()
10.3.27-MariaDB