SQL tables not joining correctly - Values only appear when specified in WHERE clause

Question

This is my query:

SELECT  

a.[Hierarchy Group ID],
o.[Opportunity ID],
o.[Opportunity Name],
o.[ARR],
o.[TCM],
p.[Product Group],
a.[Account Management Scope],


FROM OpportunityTable as o 
LEFT JOIN AccountTable as a ON o.[Account ID]=a.[Account ID] 
LEFT JOIN ProductTable as p ON o.[Product Lookup]=p.[Product ID]

WHERE 
o.[Stage] = 'Value' 
a.[Account Management Scope] in ('Value 1','Value 2','Value 3')

When I run this query, I get null values for some rows (but not all) for Hierarchy Group ID and Account Management Scope even though I know that these values are not null. However, when I add an additional "WHERE [Account ID] = 'Specific Value'" the values for Hierarchy Group ID and Account Management Scope suddenly appear for that particular account that just showed null values. Is there something wrong with the way I am joining these three tables that is causing this to happen?

Answer 1

Because you're using left join, the result will have a row for every row in OpportunityTable, whether or not there is a matching row in the other tables. When no matching row for join, you will get NULLs for the columns in the other table(s).