SQL advanced -> Concatenate data from two fields ignoring NULL values in one of them

The mission is to retrive address list of all employees concatenating street and house-number fields. In MS-Access you have to know the simple trick.

Dataset is following:
FirstName LastName city street housenum
john silver london ukitgam 780/19
gret garbo berlin akstrass 102
le chen berlin oppenhaim NULL
daniel defo rome corso vinchi 25
maggi forth london bolken str NULL
voich lutz paris pinchi platz NULL
anna poperplatz milan via domani 15/4


If you'll write following query:
SELECT Trim(a.FirstName) & ' ' & Trim(a.LastName) AS employee_name,
a.city, a.street + ' ' + a.housenum AS address
FROM Employees AS a


The result will be as this:
employee_namecityaddress
john silver londonukitgam 780/19
gret garbo berlinakstrass 102
le chen berlinNULL
daniel defo romecorso vinchi 25
maggi forth londonNULL
voich lutz parisNULL
anna poperplatz milanvia domani 15/4


Instead the correct syntax have to be:
SELECT Trim(a.FirstName) & ' ' & Trim(a.LastName) AS employee_name,
a.city, a.street & (' ' +a.housenum) AS address
FROM Employees AS a


As a result query returns street name even if house-number is NULL:
employee_namecityaddress
john silver londonukitgam 780/19
gret garbo berlinakstrass 102
le chen berlinoppenhaim
daniel defo romecorso vinchi 25
maggi forth londonbolken str
voich lutz parispinchi platz
anna poperplatz milanvia domani 15/4


Same query on SQLServer 2005, using isNull function:
SELECT RTrim(a.FirstName) + ' ' + RTrim(a.LastName) AS employee_name,
a.city, a.street + RTrim(' ' + isNull(a.housenum,'')) AS address
FROM Employees AS a;



sqlexamples.info