Dealing with MySQL NULLs

By xerocube | October 24, 2006

Yesterday, I continued my journey into the joy that is MySQL. I grew up, career-wise, in the house of SQL Server, so I am familiar with SQL and all that. However, when it comes to SQL skills, I don’t have the “mad” skills… just the “know enough to be officially dangerous” type of skills.

Anyway, I wanted to share with you one of my goals of yesterday. I needed to join two tables, getting values from the second table, if they existed, and also combining columns together in order to create a new column. Of course, I used a left join to achieve the joining goal, but I had issues when it came to concatenating field values together. I kept receiving NULLs for my new column value. This was due to there being NULL values being pulled from the second table durin the join.

So, I stumbled upon the COALESCE keyword in MySQL.  There may be something similar to this in SQL Server, but I am not aware of it at this time.  COALESCE  detects when a specified field value is NULL and then replaces it with  the given value in the parameter. This was useful for me when attempting to concatenate  string values to create a new column value.

So my sample code looks like this:

SELECT CONCAT(P.pname, ‘-‘, COALESCE(C.cname,”)) AS NewColumn1, CONCAT(p.id, ‘-‘, COALESCE(C.id,”)) AS NewColumn2
FROM Table1 as P
LEFT JOIN Table2 as C ON P.id = C.Name

Of course, many of you will have more complex uses for such a function, but for someone getting the feet wet with a different technology, I thought this was useful. Let me know if you know of any other useful functions within MySQL.

Find more information here: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html 

Topics: Code | 2 Comments »

2 Responses to “Dealing with MySQL NULLs”

Brian Says:
October 25th, 2006 at 9:29 am

COALESCE is in SQL Server as well. It takes a list of columns or statements and returns the first non-null value it comes across. Example:

if column.value is null then the returned value will be ‘hey’

COALESCE(column.value, NULL, ‘hey’)

The other useful function in this case is ISNULL. I’m not sure what the equivilent would be in MySQL but it takes 2 parameters, 1 to check for null and the second to return if the first is null. Example

if column.value is null, ‘hey’ will be returned

ISNULL(column.value, ‘hey’)

Rock on.

Brian

xerocube Says:
October 25th, 2006 at 10:41 am

I knew ISNULL() existed in SQL Server, but I believe it’s use in MySQL is different. The closest thing I could find was COALESCE, just in case you ever find yourself in a MySQL Environment.

I also found that you can’t simple use the + operator to concatenate string values, so you’d have to use MySQL’s CONCAT() function.

Comments

Archives

Blogroll

RockinMedia