Postgresql 4 table joins

join;postgresql

I have one doubt in posgresql joins and filters. Please find below sql code snippets.

1)

select count(*)
FROM lfa1 a
LEFT JOIN lfb1 b
    ON a.lifnr = b.lifnr
    and a.hvr_is_deleted = 0
    and b.hvr_is_deleted = 0
    and a.mandt = '100'
    and b.mandt = '100'
LEFT JOIN LFM1 m1
    ON a.lifnr = m1.lifnr
    and m1.mandt = '100'
    and m1.hvr_is_deleted = 0
LEFT JOIN LFM2 m2
    ON a.lifnr = m2.lifnr
    and m1.ekorg= m2.ekorg
    and m2.mandt = '100' 
    and m2.hvr_is_deleted = 0;

2)

select count(*)
FROM lfa1 a
LEFT JOIN lfb1 b
    ON a.lifnr = b.lifnr

LEFT JOIN LFM1 m1
    ON a.lifnr = m1.lifnr

LEFT JOIN LFM2 m2
    ON a.lifnr = m2.lifnr
    and m1.ekorg= m2.ekorg


WHERE
    a.hvr_is_deleted = 0
    and b.hvr_is_deleted = 0
    and a.mandt = '100'
    and b.mandt = '100'
    and m1.mandt = '100'
    and m1.hvr_is_deleted = 0
    and m2.mandt = '100' 
    and m2.hvr_is_deleted = 0;

Both literally having same meaning but logically it's not working as both of the snippets are giving different counts. I want know how controls will be passed here to execute this code.
Can someone help me to understand would be appreciated.

Thanks

Ravi

Best Answer

As indicated in comments these queries are not equivalent. To understand why the are different, let's examine them:

create table A (x int);
insert into A(x) values (1);

create table B (x int, y int);
insert into B(x,y) values (2,2);

select A.x, B.x, B.y
from A
left join B
    on A.x = B.x
    and B.y = 2;

Since no rows match the join predicate, B.x and B.y are replaced with null in the resulting relation:

A.x B.x     B.y
1   null    null

If we move B.y to the where clause:

select A.x, B.x, B.y
from A
left join B
    on A.x = B.x
where B.y = 2;

you will get 0 rows as a result. To understand why, the WHERE clause is evaluated after the JOIN, so this is the same as:

select Ax, Bx, By
from (
    select A.x as Ax, B.x as Bx, B.y as By
    from A
    left join B
        on A.x = B.x
) as T
where By = 2;

But 

    select A.x as Ax, B.x as Bx, B.y as By
    from A
    left join B
        on A.x = B.x

still evaluates to

Ax  Bx      By
1   null    null

so your where clause will be:

where null = 2

i.e.

where null

which isn't TRUE, so the result is empty

Fiddle

Related Question