SQL Case Statement

Question

I'm trying to write a SQL Query for the below. I have list of email address in table. I need to pull Subscriber key associated to that email address from another table if that email address is not present I want email address as subscriber key. How to write SQL for this? Any help will be highly appreciated.

Table1
    **| Email ID**
    | TOM@gmail
    | KIM@gmail
    | JIM@gmail
    | RIM@gmail
    | SIM@gmail 

Table2
    Subscriber key  | Email ID**
    100222221       | TOMMOT@gmail
    103333332       | KIM@gmail
    104444443       | JIMMIJ@gmail

Output:

Subscriber key  | Email ID**
    100222221   | TOMMOT@gmail
    103333332   | KIM@gmail
    104444443   | JIMMIJ@gmail
    RIM@gmail   | RIM@gmail 
    SIM@gmail   | SIM@gmail

Answer 1

Try this -

SELECT
CASE WHEN SubscriberKey IS NULL
THEN [Email ID]
ELSE SubscriberKey
END AS SubscriberKey,
[Email ID]
FROM
(SELECT 
de2.SubscriberKey,
de1.[Email ID]
FROM
[TABLE1] de1
LEFT JOIN [TABLE2] de2
on de1.[Email ID] = de2.[Email ID]
) AS ANSWER