How can I Replace an Artists' Last Name in SQL with the IN Clause?

Question

I am currently learning SQL and I am doing a SQL assignment with the following scenario:

Select the FirstName and LastName for Artists who were born in Canada or the UK. Replace any artists last name values who do not have a last name with 'NA' in your results. You must write this query using the IN() operator in the where clause.

I have the first part solved but I don't understand how to do the second part.

My code:

SELECT FirstName, LastName
FROM mArtist
WHERE HomeCountry IN ('Canada', 'UK') 
  AND LastName IN ('NA')

Expected result:

|firstname | lastname |
======================
|Harry     | Styles   |
|Shania    | Twain    |
|Drake     | na       |
|Ed        | Sheeran  |

With my code as is, nothing in the table pops up.

Answer 1

Try this:

select
    firstname
  , case when lastname is null then 'NA' else lastname end as lastname
from mArtist
where homecountry in ('Canada', 'UK')

You are filtering out the null lastname as opposed to updating it to 'na' on the result.

WHERE HomeCountry IN ('Canada', 'UK') AND LastName IN ('NA')

Both conditions must be true to return a result. Since there are no records that have an 'na' lastname value, this set of conditions returns no records. Instead, you should remove the second condition and use a case statement to update the null values to 'na' in the result.

Your 'where' clause uses the 'in' statement correctly for the country which satisfies the question. As Stu stated in his comment, a coalesce works perfectly to replace the null values also (actually probably better to use coalesce there over a case statement).

select
    firstname
  , coalesce(lastname,'na') as lastname
from mArtist
where homecountry in ('Canada', 'UK')