Solution for "System.QueryException: List has no rows for assignment to SObject" - Null Coalescing Operator (??)

Hello Trailblazers,

According to the International Survey of Issues faced by Salesforce Developers (which was NEVER conducted) by SFDC Stop: Every salesforce developer has faced the below error at least once in their developer career:

System.QueryException: List has no rows for assignment to SObject

This error mainly occurs when you're trying to perform a SOQL query which (ideally) should return a single record and you're trying to store that record in a variable of that sObject type which you're querying. Let's take a look at the below code as an example:

Contact contactRecord = [SELECT Name FROM Contact WHERE Id = '003H3000001lIHsIAM'];
System.debug(contactRecord);

If you notice above, we're querying a contact record using the id of the contact and then displaying it in the log. The result for this code snippet is shown below:

A contact with name Andy Young is queried and assigned to our contactRecord variable successfully. I checked it in our salesforce org, and the contact with the same id and name as Andy Young is present as shown below:

Till now, everything is working as expected. Now the question is: What will happen when we delete this contact record and try to execute the same code again?

Let's give this a try! I deleted this contact record from our salesforce org and now we are getting the below error when we try to open the detail page of this contact:

The requested resource (our contact record) does not exist (in our salesforce org), because we deleted this record. I executed the same code snippet to query this contact record and this time, we're getting the error "System.QueryException: List has no rows for assignment to SObject" as you can see below:

This error is coming because our contact record with id 003H3000001lIHsIAM doesn't exist in our salesforce org now. The query which we're performing is returning a list which has no rows (no records) to assign to the contactRecord variable which is of type Contact sObject. So, we can say "List has no rows for assignment to SObject".

In order to solve this error, we can use the Null Coalescing Operator (??) which is delivered by salesforce in Spring 24 release. This operator is denoted by two question marks: ?? and is a binary operator of form: x ?? y. It basically says that: If x is not null, it'll return x. Otherwise, it'll return y. Now, for our scenario, the list of contact records we're trying to query is coming as empty which is why we cannot refer to the first element of that list in order to assign it to contactRecord variable. We can modify our code to assign a default contact record when the list is coming as null. Let's see the modified code below:

Contact contactRecord = [SELECT Id FROM Contact WHERE Id = '003H3000001lIHsIAM'] ?? new Contact(LastName='Malhotra');
System.debug(contactRecord);

If you see above, at the end of the query, we've added: ?? new Contact(LastName='Malhotra');

We're using the Null Coalescing Operator ?? at the end of our contact query, keeping a new (default) instance of contact object where LastName = Malhotra. Let's see what happens when we try to execute this code:

As you can see above, instead of throwing error, our debug is now showing the default contact record with last name as Malhotra. This means that: the instance of our contact record which is after the Null Coalescing Operator is assigned to our contactRecord variable. This is because the list of contact records which were queried was coming out to be null.

Therefore, using our new Null Coalescing Operator, we're able to deal with the scenario when our query doesn't return any rows so that we don't get any error.

Points to remember about Null Coalescing Operator

Operator precedence will apply as is while using Null Coalescing Operator. For example: a ?? 25 + 50 ?? b will be evaluated as: a ?? (25 + 50 ?? b) and not (a ?? 25) + (50 ?? b). We should use appropriate paranthesis to make sure the evaluation is done correctly. Let's take a look at the result of mentioned examples below:
As we executed the above code snippet, you can see that the result for a ?? 25 + 50 ?? b is coming out to be 10 as a has the value 10. However, the result for (a ?? 25) + (50 ?? b) is coming out to be 60 as the evaluation for this will be 10 + 50 = 60.
You cannot use Null Coalescing operator as the left side of the assignment. For example: a ?? b = 50; doesn't evaluate to anything as shown below: