What type of Join Syntax is this?

Question

So I've never seen a join syntax like this before, and I am curious at what it is called and what the use case is for it?

It seems to preforms similar to when you join to a subquery, without actually creating a subquery.

from member ddm
inner join trace_execution ssn on ddm.id = ssn.id 
**left join dw_member_subscription dms 
  inner join dw_d_subscription dds on dms.id = dds.id 
  on ddm.id = dms.id** 

Answer 1

JOINs can be nested like this. It is interpreted as:

from member ddm inner join
     trace_execution ssn
     on ddm.id = ssn.id left join
     (dw_member_subscription dms inner join
      dw_d_subscription dds
      on dms.id = dds.id 
     )
     on ddm.id = dms.id

I wouldn't write SQL like this in general. However, I wish the standard required parentheses to make it clear what is going on when the JOINs are not interleaved with their corresponding ON clauses.