LEFT OUTER JOIN with WHERE acting like INNER JOIN

Lisa Callahan · 2025-03-15T11:57:04+00:00

There was an error rendering this rich post.

I have the following in a SELECT -

SELECT

[Level(Employee.Employee).Employee Key],
[Level(Employee.Employee).Termination Date]

FROM [Level(Employee.Employee)]

LEFT OUTER JOIN
SELECT [Level(Employee Work Assignment Actions.Employee Work Assignment Actions).Reason Code]
FROM [Level(Employee Work Assignment Actions.Employee Work Assignment Actions)]
/* WHERE [Level(Employee Work Assignment Actions.Employee Work Assignment Actions).Reason Code]='STATUS CHANGE NOBEN' */
ON [Level(Employee.Employee).Employee Key]=[Level(Employee Work Assignment Actions.Employee Work Assignment Actions).Employee Key]

And when I remove the comments from the WHERE clause I am only getting results that meet those conditions from both levels.

What I am after is all the records from the Employee level and only those records from the Employee Work Assignment Actions level that meet the condition in the WHERE clause.

What am I doing wrong?

Find more posts tagged with

Accepted answers

All comments

Enrico Menotti

Hi Lisa,

I think we have already discussed this some months ago. Birst is translating the BQL to Transact SQL, and in doing so it first does the join and only after that puts the WHERE. There is no way for end users to see the resulting SQL, but I'm pretty sure I'm right here, based on the behavior.

As a consequence, your condition in the WHERE applies to the result of the join, thus to both sources. There are two ways to get what you want:

A. Modify the WHERE to:

WHERE

[Level(Employee Work Assignment Actions.Employee Work Assignment Actions).Reason Code]='STATUS CHANGE NOBEN'

[Level(Employee Work Assignment Actions.Employee Work Assignment Actions).Reason Code] IS NULL

B. Move the condition in the WHERE to the ON:

[Level(Employee.Employee).Employee Key]=[Level(Employee Work Assignment Actions.Employee Work Assignment Actions).Employee Key]

AND

[Level(Employee Work Assignment Actions.Employee Work Assignment Actions).Reason Code]='STATUS CHANGE NOBEN'

@Diane McElhiney May you please confirm or correct what above? Thank you!

Hope this helps,

Enrico

Diane McElhiney

Try B.

Enrico Menotti

Hi Diane,

A. works as well, I did that multiple times and actually I prefer this way due to it being more readable in my opinion. Also, if a condition is needed on the left part of the left join, such a condition cannot be implemented in the ON. And if you want a full outer join, you must use technique A.

I was hoping you could confirm this statement of mine:

"Birst is translating the BQL to Transact SQL, and in doing so it first does the join and only after that puts the WHERE."

Also, am I right in saying that there is no way for end users to see the resulting SQL?

Thanks,

Enrico

Diane McElhiney

Users do not have access to the physical SQL issued to the Birst DW.