CODEWITHSUNDEEP
sqlsql-servert-sqljoin
yoelbenyossef
yoelbenyossef

Reputation: 463

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 whether 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

Upvotes: 1

Views: 362

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 3

Related Questions