CODEWITHSUNDEEP
apache-sparkpysparkapache-spark-sql
Vandit Goel
Vandit Goel

Reputation: 803

Compare blank string and null spark sql

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

Upvotes: 0

Views: 607

Answers (1)

Axeltherabbit
Axeltherabbit

Reputation: 753

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;

Upvotes: 1

Related Questions