yoelbenyossef yoelbenyossef - 1 month ago 8
SQL Question

Outer Join acting like an inner join

Working on this bug, and I need another pair of eyes.

This query should take all records from period_matrix whet or not it's in sales matrix, but it's not, Instead, it's only taking record where both are there, which is an inner join, not outer. This is on SQL Server

Like I said, spent an hour on this, need a fresh perspective.



select
*
from Period_Matrix PM
left outer join Sales_Matrix SM_Current
on PM.current_period = SM_Current.period
and PM.Product_Type = SM_Current.Product_Type
and PM.team_no = SM_Current.team_no
order by period desc, team_no

Answer

This is your query:

select *
from Period_Matrix PM left outer join
     Sales_Matrix SM_Current 
     on PM.current_period = SM_Current.period and
        PM.Product_Type = SM_Current.Product_Type and
        PM.team_no = SM_Current.team_no
order by period desc, team_no;

This should be doing a bona fide left join. The question is: Are you seeing the results you expect?

Well, first you have duplicate column names. More importantly, you have columns from the second table in the order by. NULL values don't always order the way you want them to. I would suggest:

select PM.*, SM_Current.??, . . .
from Period_Matrix PM left outer join
     Sales_Matrix SM_Current 
     on PM.current_period = SM_Current.period and
        PM.Product_Type = SM_Current.Product_Type and
        PM.team_no = SM_Current.team_no
order by PM.current_period desc, PM.team_no;

I am guessing this will fix your problem.

The SELECT is saying to list out all the columns from the second table, explicitly, to avoid duplicates and redundancy.