Question 112834 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How do I replace NULL values with 'N/A' in SQL?

I am looking to replace the NULL values that occur as a result of a SQL JOIN statement, with ‘N/A’.
I have tried to set the default value of both related columns from both tables to N/A, however, every time I execute the SQL JOIN statement, I still receive NULL values.

The two tables I have are the clients and Medical_Aid tables, which I have connected using a foreign key called Reg_No. Below is my sql join query

SELECT
    clients.Id_Number,
    clients.Medical_No,
    medical_aid.Name AS Medical_Aid,
    clients.First_Name,
    clients.Last_Name,
    clients.Age,
    clients.Gender,
    clients.Email,
    clients.Telephone
FROM
    clients
    LEFT OUTER JOIN medical_aid ON clients.Reg_No = medical_aid.Reg_No;

I have tried to set the default value of the Medical_No and Medical_Name as 'N/A' but every time I execute a JOIN statement, NULL values are returned on the Medical_Name column only

Therefore, I am expecting the JOIN Statement to return 'N/A' for both the Medical_No and medical_AidName

Tags:

2

Answers


  1. 0 
    SELECT
    clients.Id_Number,
    ISNULL(clients.Medical_No,'N/A'),
    ISNULL(medical_aid.Name, 'N/A') AS Medical_Aid,
    clients.First_Name,
    clients.Last_Name,
    clients.Age,
    clients.Gender,
    clients.Email,
    clients.Telephone
FROM
    clients
    LEFT OUTER JOIN medical_aid ON clients.Reg_No = medical_aid.Reg_No;
    Login or Signup to reply.
  2. 0

    For the values from the medial_aid table you can use the IsNull() function to replace a NULL with a different value:

    IsNull(medical_aid.Name, 'N/A') AS Medical_Aid

    If you want to also replace another field from the clients table when no record is found in the medical_aid table, you may need to use a CASE statement:

    CASE WHEN medical_aid.Reg_No is null THEN 'N/A' else clients.Medical_No END AS Medical_No

    This statement says that when medical_aid.Reg_No is NULL (since there was no record from the medical_aid found to join to the clients table) then output ‘N/A’, otherwise output clients.Medical_No.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search