Create Key using two columns and do a left join SQL

Question

I have two tables I would to do a left join on.

Order:

| ID | CATA | Repo | Assc |

Return:

| ID | CATB | Addl| Sumin|

I need to create a Key using ID+CATA on Order table and ID+CATB on Return Table. And do a LEFT JOIN on Order (Order LEFT JOIN RETURN). What is the easiest way to do this in one shot?

with table1 as
(
Select *,
CONCAT(ID, CATB) AS Key1
from Return 
)
Select*,
CONCAT(SUPERPNR, CATA) AS Key,
from
Order
as order
left join table1
on CONCAT(order.ID, order.CATA)=table1.Key1

Answer 1

select *
from Order ca
left join Return cb
   on (ca.id, ca.cata) = (cb.id, cb.catb);