Compare blank string and null spark sql

Question

I am writing an SQL query that joins two tables. The problem that I am facing is that the column on which I am joining is blank (""," ") on one table and null on the other.

Table A

id	col
1
2
3	SG

Table B

id	col
a	null
b	null
c	SG

source_alleg = spark.sql("""
    SELECT A.*,B.COL as COLB FROM TABLEA A LEFT JOIN TABLEB B 
    ON A.COL = B.COL
    """)

For my use case blank values and null are same. I want to do something like Trim(a.col) which will convert blank values to null and hence find all the matches in the join. Output:

id	col	colb
1	either null or blank	either null or blank
2	either null or blank	either null or blank
3	SG	SG

Axeltherabbit · Accepted Answer · 2022-09-26 09:39:27Z

1

In sql the NULL are ignored during a join unless you use a outer join or full join more information : https://www.geeksforgeeks.org/difference-between-left-right-and-full-outer-join/

if you want to convert the nulls to a string you can just use an if

select 
   if(isnull(trim(col1)),"yourstring", col1),
   if(isnull(trim(col2)),"yourstring", col2)
      from T;

answered Sep 26, 2022 at 9:39

Axeltherabbit

8148 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vandit Goel Over a year ago

I want to compare the null values with the blank values in the ON statement.

Vandit Goel Over a year ago

The data is huge so I want to avoid any updates to the table.

Axeltherabbit Over a year ago

@VanditGoel in that case you should be able to do it in the ON, for instance ON if(isnull(trim(T1.col)),"yourstring", T1.col) = if(isnull(trim(T2.col)),"yourstring", T2.col)

Collectives™ on Stack Overflow

Compare blank string and null spark sql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related