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
1 2 |
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 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

When you don’t check this option, evaluated SQL for ASE DB in my case will look like below
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
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 */ |
Thats all in this post, have a nice day and see you soon 🙂