By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH
YEARS AS
(
SELECT 2016 AS YEAR UNION ALL
SELECT 2017 UNION ALL
SELECT 2018 UNION ALL
SELECT 2019 UNION ALL
SELECT 2020 UNION ALL
SELECT 2021 UNION ALL
SELECT 2022
)
,
RECORDS AS
(
SELECT 1 ID, 2018 YEAR, 10 YEAR_COUNT UNION ALL
SELECT 1, 2020, 20)
SELECT
COALESCE(ID, 0) AS ID,
Y.YEAR,
COALESCE(YEAR_COUNT, 0) AS YEAR_COUNT
FROM YEARS AS Y
LEFT JOIN RECORDS AS R
ON R.YEAR = Y.YEAR
| ID | YEAR | YEAR_COUNT |
|---|---|---|
| 0 | 2016 | 0 |
| 0 | 2017 | 0 |
| 1 | 2018 | 10 |
| 0 | 2019 | 0 |
| 1 | 2020 | 20 |
| 0 | 2021 | 0 |
| 0 | 2022 | 0 |