Now that you know how to add, update, or remove data from a table, let’s look at some of the tools SQL provides for making decisions in queries and stored procedures. What if you want to group data and return a value of 0 if a value of the SUM is NULL, for example? What if you want to make the output of a query dependent on some condition or to evaluate parameters in a stored procedure and provide conditional feedback in the output? This chapter looks at all these scenarios and more.
21.1 Conditional functions and expressions
Do you recall that you’ve already used one function in a conditional expression a few times? That function is COALESCE. You used it in chapter 15 to concatenate the full names of authors and in chapter 20 to handle NULL values for TitleName. In the first example, COALESCE allowed you to avoid a result of NULL for concatenated full names in cases when there were null values for the MiddleName of any authors.
21.1.1 COALESCE function
SELECT CONCAT(FirstName, ' ', COALESCE(MiddleName, ''), ' ', LastName)
AS AuthorName
FROM author;