0

these two query output different result,
I have question AND m.status = $1 ...each condition follow the left join table or move to the final part, thats different ??

query 1

SELECT count(mua.*) 
  AS total_row_count
  FROM media_user_action mua
    LEFT JOIN media m ON m.id = mua.media_id
      AND m.status = $1
    LEFT JOIN gallery_media gm ON gm.id = mua.media_id
    LEFT JOIN gallery g ON g.id = gm.gallery_id
      AND g.status = $1
    LEFT JOIN "user" mcbu ON mcbu.id = m.create_by_user_id
      AND mcbu.status = $1
    LEFT JOIN "user" gcbu ON gcbu.id = g.create_by_user_id
      AND gcbu.status = $1
WHERE mua.user_id = $2

query 2

SELECT count(mua.*) 
  AS total_row_count
  FROM media_user_action mua
    LEFT JOIN media m ON m.id = mua.media_id
    LEFT JOIN gallery_media gm ON gm.id = mua.media_id
    LEFT JOIN gallery g ON g.id = gm.gallery_id
    LEFT JOIN "user" mcbu ON mcbu.id = m.create_by_user_id
    LEFT JOIN "user" gcbu ON gcbu.id = g.create_by_user_id
WHERE 
m.status = $1
AND g.status = $1
AND mcbu.status = $1
AND gcbu.status = $1
AND mua.user_id = $2

UPDATE
base on below answer

if I want make sure return result, must all related/left join table status both equal $1, ( join table recode could be null) so I have to add AND x.status .. follow the join table, right?

Improve this question
2
  • 1
    are you asking "which of the two should you use?"? Because the answer would depend on your intent. Commented May 12, 2016 at 16:00
  • thanks for reply, I want to select each related table all status are both equal $1 and where mua.user_id = $2 Commented May 12, 2016 at 16:16

3 Answers 3

Reset to default
2

When you use left outer join, value of the right table may be NULL.

For simplicity's sake, let say we have

Table A (id, name) and Table B (fid, status)

Then query1 will be like

select A.id, B.status 
from A
left join (select * from B where status = $1)
on A.id = B.fid;

so result could have B.status is NULL

And query2 will be like

select C.* 
from (select A.id, B.status 
      from A
      left join B
      on A.id = B.fid
) C
where C.status = $1;

It's equal to

select * 
from A
inner join B
on A.id = B.fid
where B.status = $1;

So B.status must exactly be $1, and is never NULL

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

thanks for the example, does that means if I want to select/filter both A, B status both equal $1 . and if B may be null then I should use query1, and if B always not null then I can also use query2 ?
It's depend on your intention. Let say: We have a guy A. All friends who know (id) A are smart (status). Now 1.You must get list of all people who know A (some friends A know - NOT NULL, some strangers A don't know - NULL) => query1. 2.You must get all friends who know A (not null) => query2.
2

When you put WHERE conditions on tables that you have LEFT JOIN-ed, and that require some their fields to have non-NULL values, then you are actually converting the LEFT JOIN into an INNER JOIN.

This is because a LEFT JOIN may produce results where the joined table has no matching record. In that case all fields of that "virtual" record have value NULL. So by requiring that one of those fields is not null, you remove those instances from the result.

If on the contrary, you put such conditions in the LEFT JOIN condition, then you do not break this mechanism, where the a non-match will still give a result, albeit with NULL.

Improve this answer

4 Comments

thanks for the explanation, so if I'm sure the join table are not null then I can put the and ... at the final or if the join table could be null then I should put and ... follow the join table right?
Right. That's how it is.
and if I want original table only return rows (must all related/left join table column equal what I desired or even null ) then I should put and ... at the final right? (and this just like inner join )
Yes, indeed, the first table in the from clause (at the left of all the joins), should be filtered by conditions in the where clause.
2

An outer join means that additionally to an inner join, when no match is found a mock record with all columns null gets joined.

So let's say for one mua we don't find a matching m. Then we get an m record with all columns null. m.status is null. With WHERE m.status = $1 you dismiss that record, however, so you are where you were with a simple inner join.

Criteria on outer-joined tables belongs in the ON clause.

Improve this answer

4 Comments

thanks for the explanation, so if I'm sure the join table are not null then I can put the and ... at the final or if the join table could be null then I should put and ... follow the join table right?
The question is: Do you want to have mua records in your result for which not exist any m records with the desired status? If not, then you don't use a LEFT [OUTER] JOIN, but a simple [INNER] JOIN and it doesn't really matter whether you have m.status = $1 in WHERE or ON. If yes, you use a LEFT [OUTER] JOIN and criteria belongs in the ON clause.
I don't wnat mua show in result if other join table status not what match what I want or if other table row not exist, but I have to use left join for if m record exist, then output result match data structure what I want
No. LEFT JOIN or LEFT OUTER JOIN is a special join made especially to keep records with no match, an outer join. You however want a normal join, i.e. INNER JOIN or simply JOIN: FROM media_user_action mua JOIN media m ON ....

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.