user1575921 user1575921 - 6 months ago 13
SQL Question

select left join table use and condition

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?

Answer

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

Comments