presto sql: multiple join with `using` statement

Question

I have multiple tables and I join them (they share the same key) like this

select *
from user_account_profile
inner join user_asset_profile
using (user_id)
left join user_trading_profile
using (user_id)

I want to know how will the key user_id be used?, is it equivalent to

select *
from user_account_profile t1
inner join user_asset_profile t2
on t1.user_id = t2.user_id
left join user_trading_profile t3
on t1.user_id = t3.user_id

or

select *
from user_account_profile t1
inner join user_asset_profile t2
on t1.user_id = t2.user_id
left join user_trading_profile t3
on t2.user_id = t3.user_id

or if these queries are all equivalent?

Answer 1

Your two versions are functionally equivalent (except for the obvious difference of a duplicated user_id column when not using using). The first inner join mandates that the two user_ids have the same value, so either comparison returns the same result set.

If you had a series of left joins then you would be requiring that the value be in the first table, and the equivalent would be t1.user_id.

If you had full joins, then you would not know. The matching would be from the table that has a value on the row. If both tables have a value, the logic says that they are the same, so it doesn't make a difference.