SQL Where Condition that ignores null and blank spaces-CodePudding

insert into Dim_Business_Line(Business_Line_Code,Business_Line_Name) 
select max(tbl.Business_Line_Code) as Business_Line_Code, tbl.Business_Line_Name 
from temp_Business_Line tbl 
LEFT JOIN Dim_Business_Line bl ON  tbl.Business_Line_Name = bl.Business_Line_Name  
where IFNULL(tbl.Business_Line_Name, '')<>''  
AND bl.Business_Line_Name IS NULL  
Group By tbl.Business_Line_Name;

In this code, in where conditon IFNULL(tbl.Business_Line_Name, ' ')<>' ' is where i am stuck, for this my trainer said it is a condition that ignores null and blank spaces .Can anyone explain How?

CodePudding user response：

IFNULL(tbl.Business_Line_Name, '') returns '' string if tbl.Business_Line_Name is NULL. <> operator is excluding all '' strings.

CodePudding user response：

More the bl.Business_Line_Name being NULL and still having a JOIN on just this value is slightly surprising.

When tbl.Business_Line_Name is NULL or is not NULL the joining is different. A LEFT join would use a subset.

Using IFNULL makes reading, and the entire logic, quite difficult.

LEFT JOIN Dim_Business_Line bl ON  tbl.Business_Line_Name = bl.Business_Line_Name  
WHERE IFNULL(tbl.Business_Line_Name, '') <> ''  
AND bl.Business_Line_Name IS NULL

I cannot guess what you are querying. It would actually imply:

LEFT JOIN Dim_Business_Line bl ON  tbl.Business_Line_Name = bl.Business_Line_Name  
WHERE tbl.Business_Line_Name IS NOT NULL
AND tbl.Business_Line_Name <> ''  
AND bl.Business_Line_Name IS NULL

But that would yield no records. Maybe an OUTER join or an OR. No idea.

About spaces, you might use the TRIM function.