Suppose I need to withdraw the managers of the employees, and if the employee doesn’t have a manager, do I withdraw NULL ?

EMPLOYEES table
It has two columns, employee_name and manager_name

If you write SELECT employee_name, manager_name FROM EMPLOYEES

Displays an empty field in the column manager_name , if there is no manager, but you need to output NULL

  • LEFT JOIN will help you. If these two words are not clear - @BOPOH is right: tables to the studio - cyadvert
  • So what? if the employee does not have a manager, hence the column manager_name will be empty ... What is needed? - cyadvert

1 answer 1

After a joint discussion with @ BOPOH, this option is proposed:

 SELECT employee_name, NULLIF(TRIM(manager_name),'') as manager_name FROM EMPLOYEES;

Note: this option is suitable if you want to output exactly NULL . If not NULL , but something else, then this one will do:

 SELECT employee_name, IF(CONCAT(manager_name,'')='', [что-то другое], manager_name) as manager_name FROM EMPLOYEES;

I in such cases still TRIM() add just in case: IF(CONCAT(TRIM(manager_name),'')='', NULL, manager_name)

  • Wouldn't it be easier to do nullif(trim(manager_name), '') right nullif(trim(manager_name), '') ? - BOPOH
  • @BOPOH checked: does not work when compared with '' . Always NULL . - cyadvert
  • what exactly does not work? nullif(exp1, exp2) returns null if exp1 = exp2, otherwise returns exp1, i.e. nullif(trim(manager_name), '') returns null only when trim (manager_name) = '' or when trim (manager_name) = null. - BOPOH
  • Here, I drove through one of my bases: nullif(trim( unitEn ),'') unitEn // ------------------------- -------- // (NULL) (NULL) // (NULL) // lb lb If unitEn =NULL , the answer is NULL ; if unitEn=' ', the answer is NULL ; if unitEn = 'lb ' - the answer is 'lb' - cyadvert
  • well, and should work? or am I confusing something? - BOPOH