function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Andrew McGrathAndrew McGrath 

SOQL - Developer Console - NOT NULL operator

Hi

Been looking around with no luck - is there a simple equivalent to the SQL not null statement in SOQL (ie return rows where the column contains a value?)

Current query:

SELECT Id, Name, customfield__c from Account
Where customfield__c !=null

Any help greatly appreciated! I feel the answer is obvious but it is driving me mad. 

Cheers



 
Best Answer chosen by Andrew McGrath
Alexander TsitsuraAlexander Tsitsura
Hi Andrew,

For example, if u have two contacts:

Contact1(FirstName = null, LastName = 'First')
Contact2(FirstName = 'Junk', LastName = 'Second')
 
  • This query return Contact1, where Frist Name not populated(null)
SELECT Id, FirstName FROM Contact WHERE FirstName = null
  • This query return Contact2,where Frist Name are populated('Junk')
SELECT Id, FirstName FROM Contact WHERE FirstName != null

Thanks,
Alex

All Answers

Alexander TsitsuraAlexander Tsitsura

Hi Andrew,

Yes, your query is correct, for filter not null value use "!= null"

As a common practice, if your question is answered, please choose 1 best answer. 
But you can give every answer a thumb up if that answer is helpful to you.

Thanks,
Alex
Andrew McGrathAndrew McGrath
Hi Alexander Thanks for the answer - however that appears to return NULL rather than NOT NULL? Andy
Alexander TsitsuraAlexander Tsitsura
Hi Andrew,

For example, if u have two contacts:

Contact1(FirstName = null, LastName = 'First')
Contact2(FirstName = 'Junk', LastName = 'Second')
 
  • This query return Contact1, where Frist Name not populated(null)
SELECT Id, FirstName FROM Contact WHERE FirstName = null
  • This query return Contact2,where Frist Name are populated('Junk')
SELECT Id, FirstName FROM Contact WHERE FirstName != null

Thanks,
Alex
This was selected as the best answer
Andrew McGrathAndrew McGrath
Cheers Alex - much appreciated. 
Learner21Learner21
Hi Alexander,

I have around 12 fields. Trying to write a SOQL query for more than one field to exclude null values.

For single field which is not null, I am able to get the records. I want to a SOQL to get the records for all 12 fields which are not null.

Below is the SOQL to get the records for single field which is not null:
SELECT Name ,AccountNumber, OwnerId,ParentId, Data_Center_BDE__c,Tele_CDE__c,DMS_Owner__c,Primary_Local_Market_Rep_1__c,Information_Governance_Rep__c,DM_Cloud_Sales_Rep__c,Primary_BDE__c,Primary_CDE__c,Primary_BDE_DB_R__c,Primary_CDE_DB_R__c,Library_Services_BDE__c FROM Account WHERE Data_Center_BDE__c != NULL 

similarly I want to write for other 12 fields which are not null. Can you please suggest.
manu manu 23manu manu 23
Hi Learner21, 

In soql, there is no shortcut, if you have to check if 12 fields are not null you need to mention all 12 fields like below.  

[SELECT name, rating,industry, phone, Number__c FROM  account WHERE (rating!=null and industry!=null and phone!=null and  name!=null and Number__c!=null)] 
 //i just mentioned 5 fields. However, if you have to mention 12 fields, add upto 12 in the same way. 
Learner21Learner21

Thanks, Manu, Below query is working for my question.

SELECT Name ,AccountNumber, OwnerId,ParentId, Data_Center_BDE__c,Tele_CDE__c,DMS_Owner__c,Primary_Local_Market_Rep_1__c,Information_Governance_Rep__c,DM_Cloud_Sales_Rep__c,Primary_BDE__c,Primary_CDE__c,Primary_BDE_DB_R__c,Primary_CDE_DB_R__c,Library_Services_BDE__c FROM Account where Data_Center_BDE__c != NULL and Tele_CDE__c != NULL and DMS_Owner__c != NULL and Primary_Local_Market_Rep_1__c != NULL and Information_Governance_Rep__c != NULL and DM_Cloud_Sales_Rep__c != NULL and Primary_BDE__c != NULL and Primary_CDE__c != NULL and Primary_BDE_DB_R__c != NULL and Primary_CDE_DB_R__c != NULL and Library_Services_BDE__c != NULL
Vijayalakshmi SubramanianVijayalakshmi Subramanian
I want to check how many fields are null how many fields are having values? how to count it how many null and how many not null in a object