Why my Right Join does not get me the Region West?-CodePudding

I have this dataset

CodePudding user response：

Maybe you wanted to do this:

WITH cte as (
              SELECT distinct region 
              FROM #RegionSales )
SELECT * 
FROM cte
LEFT JOIN #RegionSales rs ON rs.Region = cte.Region AND rs.Distributor='ACE';

see: DBFIDDLE

Because when doing a RIGHT JOIN like this:

WITH cte as (
              SELECT distinct region 
              FROM #RegionSales )
SELECT * 
FROM #RegionSales rs
RIGHT JOIN cte ON rs.Region = cte.Region ;

You will not get any Region/Distribute with values West/ACE, see DBFIDDLE2

CodePudding user response：

This is the way to go:

   WITH cte as (
                  SELECT distinct region 
                  FROM RegionSales )

SELECT *
FROM      RegionSales as rs
RIGHT JOIN cte                    ON rs.Region = cte.Region AND rs.Distributor = 'ACE'