0

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

  • 1
    These two queries do not have the same meaning, so it follows they return different results. – mustaccio May 21 at 18:56
  • The WHERE clauses in the second query turn the outer joins back into inner joins – a_horse_with_no_name May 22 at 6:31
0

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.