String concatenation in SQLITE with NA values?

Currently working in SQLite and have the following table

id subject_1  subject_2 
1  maths       algebra
2  english     <NA>
3  french      speech

I would like to create a new column – which is the concatenation of the columns subject_1 and subject_2, however I have values within my subject_2 column – is there a way of concatenating the columns so that I can get the following table

id new_col
1  maths algebra
2  english
3  french speech

Now i have tried the two following methods – both of which did not work; concatenation using || and coalesce (which only takes the first non null value)

select
id 
subejct_1 | ''| subject_2 as new_col 
from my_table 

coalesce is not an option as i would like all string outputs together from both columns.

Is there a way of convering the NA values before hand to an empty string instead?

Answer

If you are sure that subject_1 does not contain nulls then use coalesce only for subject_2:

select id,
  subject_1 || COALESCE(' ' || subject_2, '') new_col
from tablename 

See the demo.
Results:

| id  | new_col       |
| --- | ------------- |
| 1   | maths algebra |
| 2   | english       |
| 3   | french speech |