-1

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

nbk
  • 45,398
  • 8
  • 30
  • 47
Lehlohonolo Nkosi
  • 1
  • 1
  • 2
    Use COALESCE() function. – Akina Nov 06 '22 at 10:15
  • 2
    _"I am looking to replace the NULL values that occur as a result of a SQL JOIN statement, with 'N/A'."_ - hold on, **why?** do you want to do this (in SQL)? You shouldn't be using SQL for _presentation-layer_ tasks... `NULL` is _special_ in SQL and behaves differently to non-`NULL` values, that's why _relabelling_ `NULL`s is a bad idea because you lose the benefits of its special behaviour. – Dai Nov 06 '22 at 10:19
  • Thank you so much, the COALESCE() Function worked perfectly fine. – Lehlohonolo Nkosi Nov 06 '22 at 19:39

1 Answers1

-1
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;
Angrigo
  • 61
  • 7
  • 1
    COALESCE is much better since it will do on every DB. Using non-standard functions without needing it is no good idea. – Jonas Metzler Nov 06 '22 at 11:17
  • MySQL's `ISNULL(x)` only takes 1 argument and returns either `1` or `0` - it isn't like MSSQL's `ISNULL(x,y)` which is more like `COALESCE`. – Dai Nov 06 '22 at 11:35