Select a new column based on values of two column from two tables with null if no data is present

user_10260448 0
Viewed 206

How to select a new column based on values of two column from two tables with null if no data is present.

I have two tables

table1 with column1 and table2 with column2. I need to select data from both these columns in column3 (column3 would be a part of table1) in a way that:

column1     column2     column3
-------     -------     -------
1           2           null
2           3           present
3                       present
4                       null

If the value of column2 is present in column1

=> I need to be able to assign a string(lets say "present") in column3

else the value in column3 should be null

At present I am using join but I wasnt able to assign the null part to column3

Thanks in advance.

apache-spark-sql apache-spark sql
2 Answers
 create table table1(column1 int);
 create table table2(column2 int);

 insert into table1 values(1);
 insert into table1 values(2);
 insert into table1 values(3);
 insert into table1 values(4);
 
 insert into table2 values(2);
 insert into table2 values(3);

Query:

 select column1, (case when column2 is not null then 'Present' else Null end) Column3 
 from table1 left join table2 on column1=column2

Output:

column1 Column3
1 null
2 Present
3 Present
4 null

db<>fiddle here

user_86516010

if col1 and col2 don't have duplicate value, you can use:

select t1.col1 , case when t2.col2 is not null then 'present' else null end 
from table1 t1
left join table2 t2
on t1.col1 = t2.col2
user_13674540
Related