SQL server query - join condition

Question

I am attempting to select date based on the condition and populate it in dat_Transformed using the below query. The query gets executed but the dat_Transformed is not getting populated correctly.

Any suggestions to improve the query would be appreciated.

SELECT
    dat_Transformed, 
    CASE
        WHEN EXISTS (SELECT date FROM dat_base WHERE dat_base.id= report.id) 
        THEN (SELECT date FROM dat_base WHERE dat_base .id= report.id)
        ELSE ''
    END AS dat_new
FROM report;

Answer 1

can do left join and coalesce will throw empty string in case of null

SELECT
  dat_Transformed,
dat_new = COALESCE(dat_base.date,'')
  
from report
left join dat_base on dat_base.id = report.id
;

Answer 2

LEFT JOIN should be able to help with it. Try with the below query.

SELECT
    r.dat_Transformed, 
    COALESCE(db.date, '') AS dat_new
FROM report r
LEFT JOIN dat_base db ON db.id = r.id;