Search code examples
sqlsql-serverselectcoalesce

SQL Server ISNULL / Coalesce still returning empty lists

I have a table called Users with a column called Limit that holds integer values. Every user has a Limit but non-registered users have a default limit of 1.

I want to run a simple query that tells me the Limit for a user if they are registered or defaults to 1 if they are not a registered users, however when I run a query with either ISNULL or Coalesce as written below:

SELECT coalesce(Limit,1) AS limit 
FROM User 
WHERE userID = 'testUser'

The query will work fine if I query on an existing user but will return an empty list if I try a non-registered user [it should return a single row with a value of 1].

Using ISNULL instead of coalesce produces the same result. Every example I have found of ISNULL and coalesce look that way. What am I missing?

Solution

  • coalesce operates on an returned value, and evaluates if it's null or not. In your case, a non-existent just doesn't have a row, so, as you noted, it will not work.

    You can, however, emulate this behavior with a left join:

    SELECT    COALESCE(b_limit, a_limit) AS limit
FROM      (SELECT 1 AS a_limit) a
LEFT JOIN (SELECT limit AS b_limit
           FROM   user
           WHERE  userID = 'testUser') b ON 1 = 1