sql - Flagging records with all missing values on right table when doing a left join

Question

I have two tables representing client data for different years Left Table: 2019 and Right Table: 2018. Some of these clients did not exist for the prior year and my assumption is that when doing the left join, all field values from right table will be missing for those clients. Is there a way to flag these clients (or records)? Below is my query

create table joinedTable as
select a.unique_number, a.monthly_spend, b.unique_number, b.email_preference, b.client_tier,
from table2019 as a
left join table2018 as b
on a.unique_number = b.unique_number

Below is the desired output.

unique_number	monthly_spend	email_prefernce	client_tier	Flag
12AB56FG	2000	Yes	Special	0
32AB56FG	1200	Yes	Special	0
42AB56FG	2010	Yes	Special	0
56AB56HG	3000	Yes	Special	0
72AB58FG	6000	NULL	NULL	1
92AB56FG	800	NULL	NULL	1

question from:https://stackoverflow.com/questions/65599055/flagging-records-with-all-missing-values-on-right-table-when-doing-a-left-join

Answer 1

Just use a case expression:

create table joinedTable as
    select a.unique_number, a.monthly_spend, b.unique_number, 
           b.email_preference, b.client_tier,
           (case when b.unique_number is null then 1 else 0 end) as flag
    from table2019 a left join
         table2018 b
         on a.unique_number = b.unique_number;