---

Return to the SQL Tips

---

Converting Nulls in SQL Left Outer Joins

Q. I want to use an SQL outer join between my Customer and SalesRep tables to produce a list of ALL customers and the names of the sales rep assigned to them. How can I get something other than null for the SalesRep.Name column when a customer doesn't have an assigned rep? A. Use the IfNull function to convert null to any suitable value.

Here's an example:

Select Customer.CustId,
       Customer.Name                           As 'Customer Name',
       IfNull( SalesRep.Name, 'Not assigned' ) As 'Sales Rep Name'
  From Customer
         Left Outer Join
       SalesRep
         On Customer.CustId = SalesRep.CustId

The IfNull function returns either the first argument if it's not null or the second argument if the first argument is null. The Coalesce function is an alternative that takes two or more arguments and returns the first one that's not null (or null if all arguments are null).

[report a broken link by clicking here]

---