SQL ON conditions in left join

When you use SQL for your daily work, you probably can don’t know that when you use JOIN then where is not the only way to set a condition, but let me explain this for you step by step.

1.Let’s assume that you have 2 tables. Content of first one

2.Content of the second one

3. Now let’s get only this employees with salary bigger than 4 000

4.Result shouldn’t impress anyone

5.But now let’s change our query and move condition to ON

6. Result will look different than before

7. Of course, this behaviour is correct and refers to the algebra of the relationship used in SQL. I have to paste here my favourite image

8. So as you see our statement without any statement should return

and because we just have only one entry which passes our criteria we just get empty rows in other cases. This also explains why our condition move doesn’t change anything in case of an inner join

If you created a infoset object on SAP BW System, you can see that there is an option called Left Outer: Add Filter Value to On-Condition.

When you don’t check this option, evaluated SQL for ASE DB in my case will look like below

And when we check flag:

Thats all in this post, have a nice day and see you soon 🙂