In mssql (not sure of other dialects) you can drop the inner for join, left and right. You only need outer on the full outer join. In 25 years of working in SQL I have never come across a legitimate use for Right Join. RJ means you don't understand your data.
It's pronounced Diane. I do data architecture, operations, and backend development. In my spare time I maintain Massive.js, a data mapper for Node.js and PostgreSQL.
I've used right joins a handful of times, most recently joining a table of weather stations to a CTE representing intervals during which stations were or weren't deployed (the latter being of primary interest). Left joins are certainly much more intuitive in almost all cases, but I suspect that has more to do with English-readers being used to processing left to right than anything else.
In mssql (not sure of other dialects) you can drop the inner for join, left and right. You only need outer on the full outer join. In 25 years of working in SQL I have never come across a legitimate use for Right Join. RJ means you don't understand your data.
I've used right joins a handful of times, most recently joining a table of weather stations to a CTE representing intervals during which stations were or weren't deployed (the latter being of primary interest). Left joins are certainly much more intuitive in almost all cases, but I suspect that has more to do with English-readers being used to processing left to right than anything else.
In which case it should have been
SELECT CTE.column ,STATION.data FROM CTE LEFT JOIN STATION ON STATION.CTE_ID = CTE.ID