outer join -- restriction on From clause

Question

I am new to SQL. The problem I am having is I try to logically understand that restriction on From clause when I use LEFT JOIN, I have learned restriction on where clause, but I have never seen this before. I read this article,https://stackoverflow.com/questions/8311096/whats-the-difference-between-where-clause-and-on-clause-when-table-left-join#= but I am still confused.

so my code is this.

Select e.last_name
      ,e.first_name
      ,e.marital_status_code
      ,ms.short_desc
from entity e
left join marital_status ms 
     on e.marital_status_code = ms.marital_status_code AND ms.marital_status_code = 'M'
where e.last_name = 'Hello'
order by 3
;

I totally understood when I put restriction on Where clause

like

where e.last_name = 'Hello'
AND marital_status_code = 'M'
 order by 3
    ;

please help me what is going on/how the logic works when I put restriction on From clause like this

left join marital_status ms 
         on e.marital_status_code = ms.marital_status_code AND ms.marital_status_code = 'M'

score 0 · Accepted Answer · answered May 10 '16 at 20:09

left join marital_status ms 
     on e.marital_status_code = ms.marital_status_code AND ms.marital_status_code = 'M'

This is a condition on the join.

That is a restriction on when to match two row. So this will find a match in the other table when both tables have the same marital_status_code and when marital_status_code is = 'M'in the marital_table.

This IS THE SAME as not having the join and saying

WHERE e.marital_status_code = 'M'

if there is a just one row in the marital_status table with a marital_status_code of 'M'

Or it IS THE SAME as doing nothing if there is no such row in the marital_status table.

outer join -- restriction on From clause

1 Answers1