Join help in SQL

I don't believe you need a join, but you need union:

select t1.client_id, t1.identifier, CONCAT(t1.code,t1.startdate,t1.enddate,t1.ssc)
from #temp1 t1
union all
select t2.client_id, t2.identifier, CONCAT(t2.code,t2.startdate,t2.enddate,t2.ssc)
from #temp2 t2

maybe you need a where part to restrict the result for certain rows.
See the demo

Here's what you WOULDN'T do, just posting because you asked about JOIN. It's definitely the wrong way to go, but:

select 
  COALESCE(t1.client_id, t2.client_id) client_id,
  COALESCE(t1.identifier, t2.identifier) identifier,
  COALESCE(
    CONCAT(t1.code,t1.startdate,t1.enddate,t1.ssc),  
    concat(t2.code,t2.startdate,t2.enddate,t2.ssc)
  )
from 
  #temp1 t1
  full outer join 
  #temp2 t2 
  on 0 = 1

A full outer join between these tables on an impossible condition means you end up with a resultset like:

t1.client_id t2.client_id
9908         NULL
NULL         9908

The COALESCE brings the split-ness of it back together:

client_id
9908
9908

As noted, don't do it- it's a huge waste of the database's time and resources compared to union; I purely wrote it as an example of how vertical growth of a resultset can be achieved using JOINs and also as an aid to your understanding of db theory and operation:

A UNION B (number is id)

Results grow vertically:
A1
A2
B1
B2

A JOIN B (number is id)

Results grow horizontally:
A1 B1
A2 B2

Outer joins preserve the row from the table even if there is no match:

A OUTER JOIN B

Results:
A1   null
null B2

By making the join impossible, a full outer join will cause a resultset that grew horizontally and vertically:

A OUTER JOIN B ON 1 = 0

Results:
A1   null
A2   null
null B1
null B2

COALESCE returns the first non-null argument, so if we COALESCE(a_column, b_column) it collapses those two columns, one of which is null, into one column:

acol bcol  COALESCE(acol, bcol)  result
----|-----|--------------------|--------
A1   null  COALESCE(A1, null)   -> A1
null B2    COALESCE(null, B1)   -> B1

What you seem to look for is a UNION select construct, not a join.

UNION returns rows from one sub-query and appends rows from another sub-query

So with your example

select t1.client_id, t1.identifier, CONCAT(t1.code,t1.startdate,t1.enddate,t1.ssc
from #temp1 t1 
where ... some condition ...
union all 
select t2.client_id, t2.identifier, concat(t2.code,t2.startdate,t2.enddate,t2.ssc)
from #temp2 t2 
where ... some condition ...

See docs here: UNION

Regarding your desired result I understand you need to use UNION ALL instead of join the tables.

Please check the script :

SELECT
    t1.client_id
   ,t1.identifier
   ,CONCAT(t1.code, t1.startdate, t1.enddate, t1.ssc)
   --,CONCAT(t2.code, t2.startdate, t2.enddate, t2.ssc)
FROM #temp1 t1
UNION ALL
SELECT 
    t2.client_id
   ,T2.identifier
   ,CONCAT(t2.code, t2.startdate, t2.enddate, t2.ssc)
FROM  #temp2 t2

This is what I would use to get that output:

select 
    t1.client_id, 
    t1.identifier, 
    CONCAT(t1.code,t1.startdate,t1.enddate,t1.ssc) ssc_concatenated
    --concat(t2.code,t2.startdate,t2.enddate,t2.ssc)

from #temp1 t1

union all

select 
    t2.client_id, 
    t2.identifier, 
    concat(t2.code,t2.startdate,t2.enddate,t2.ssc) ssc_concatenated
from #temp2 t2

Here's the db<>fiddle demo