When an SQL query returns null, I'm wondering if it's possible to have it return a specific string like "Unknown" instead of null.
I'm currently doing this on the JavaScript side and was wondering if there's a faster way to do this on the server.
Example (I realize the following syntax doesn't exist):
SELECT Customers.Email (RETURN "Unknown" IF NULL)
I imagine it's possible with a CASE? But filling up my query with CASE statements will slow this whole action down rather than speed it up.
While COALESCE is the best solution, there is also CASE
This can be handy if you have to deal with either nulls or empty strings.
SELECT CASE Customers.Email
WHEN NULL THEN 'Unknown'
WHEN '' THEN 'Unknown'
ELSE Customers.Email
END AS Email
FROM Customers;
SELECT CASE
WHEN Customers.Email IS NULL THEN 'Unknown'
WHEN TRIM(Customers.Email) = '' THEN 'Unknown'
ELSE Customers.Email
END AS Email
FROM Customers;
COALESCE the best solution? IFNULL seems so much simpler and easier to read.
COALESCE: Returns the first non-NULL value in the list, or NULL if there are no non-NULL values. You can use IFNULL to substitue a single value, while COALESCE takes a list, IFNULL(IFNULL(customer.email, customer.altemail),'unknown') becomes this COALESCE(customer.email, customer.altemail, 'unknown') Since COALESCE is more powerfull I always use this. Didn't experience any performace impacts so far.