3

I am generating a SOQL statement dynamically and under certain conditions I want the query to return 0 results.

The best/most concise way that I could come up with to do this is by appending an Id = null to my query clause as follows:

select Id, Name from Contact where Id = null

Is this the best way to guarantee a query with 0 results?

The reason I ask is because in SQL you can add an arbitrary comparison of numbers such a 1 = 0, but this does not seem to work with SOQL.

2
  • Does where 1=0 work?
    – WernerCD
    Jan 25, 2018 at 2:52
  • @WernerCD - it works with SQL (i.e. MS SQL Server, Oracle, MySQL), but not SOQL based on my findings.
    – dana
    Jan 25, 2018 at 6:52

3 Answers 3

5

Yes Id=null is the shortest possible filter which will never return results.

3
  • 1
    For all normal tables, anyways. There's a few tables that actually do have Id values that are all null.
    – sfdcfox
    Jan 25, 2018 at 1:13
  • @sfdcfox Which tables can be queried with SOQL and have null Id values?
    – Mike Chale
    Jan 25, 2018 at 3:45
  • @sfdcfox - im curious too
    – Eric Luu
    Jan 26, 2018 at 20:54
8

In addition to Id = null, you can instead use the somewhat clearer limit 0 (similar to top 0 in SQL, but goes at the end of the query like select Id, Name from Contact Limit 0).

2

There are other attribute checking I could think of beside Id= null are as follows:

  • OwnerId = null

  • CreatedById = null

  • CreatedDate = null

1
  • 1
    OwnerId won't work for detail objects in master-detail relationships, and while CreatedById and CreatedDate are theoretically safe, but might fail in extraordinary ways in really, really old orgs.
    – sfdcfox
    Jan 26, 2018 at 22:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.