SQL ON conditions in left join

Even if you use SQL for your daily work, it is possible that you might don’t know about two ways to set a condition: using “where” and .. put the condition into the join. The main issue is that they can return different results. Let me explain more.

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

   select * from employees emp left join salaries sal on emp.id = sal.employee
   where sal.salary > 4000

4. Result shouldn’t impress anyone

5. But now let’s change our query and move the condition to ON. Results will look different than before

6. Of course, this behavior is correct and refers to the algebra of the relationship used in SQL. I have to paste here my favorite image Image source: https://external-preview.redd.it/M5QHWsp2vgZ-3QDZ4m-qS58lsOUgDNHau8trSFzS8H0.jpg?auto=webp&s=cae9cdc438b71c9025d40dad4650801fdcae1ef8

7. So as you see our statement without any restriction 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

select
"T1"."TCTSYSID"  as "F1"
, "T1"."TCTJOBNAME"  as "F2"
from
"/BI0/ATCTHP24O00" "T1"
join
"/BI0/ATCT_O4100" "T2"
on
"T1" . "TCTSYSID"
= "T2" . "TCTSYSID"
join
"/BI0/ATCTHP24O00" "T3"
on
"T1" . "TCTSYSID"
= "T3" . "TCTSYSID"
where
( ( ( ( ( (
"T1"."TCTSYSID"
= 'NPLCLNT100'
) ) ) ) AND  ( ( ( (
"T1"."CALDAY"
= '20190414'
) ) ) ) ) )
group by
"T1"."TCTSYSID"
,"T1"."TCTJOBNAME"
order by
"F1" ASC
, "F2" ASC
plan '
(use optgoal sap_olap)
(use fact_table T3)
(use parallel 4)
(prop T3 (parallel 4))
'
/* BW-SYS-DB-SYB:I */

And when we check flag:

select
"T1"."TCTSYSID"  as "F1"
, "T1"."TCTJOBNAME"  as "F2"
from
"/BI0/ATCTHP24O00" "T1"
join
"/BI0/ATCT_O4100" "T2"
on
"T1" . "TCTSYSID"
= "T2" . "TCTSYSID"
join
"/BI0/ATCTHP24O00" "T3"
on
"T1" . "TCTSYSID"
= "T3" . "TCTSYSID"
where
( ( ( ( ( (
"T1"."TCTSYSID"
= 'NPLCLNT100'
) ) ) ) AND  ( ( ( (
"T1"."CALDAY"
= '20190414'
) ) ) ) ) )
group by
"T1"."TCTSYSID"
,"T1"."TCTJOBNAME"
order by
"F1" ASC
, "F2" ASC
plan '
(use optgoal sap_olap)
(use fact_table T3)
(use parallel 4)
(prop T3 (parallel 4))
'