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.

Improve this question
2
  • Does where 1=0 work?
    – WernerCD
    Commented 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
    Commented Jan 25, 2018 at 6:52

3 Answers 3

Reset to default
5

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

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

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).

Improve this answer
2

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

  • OwnerId = null

  • CreatedById = null

  • CreatedDate = null

Improve this answer
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 ♦
    Commented Jan 26, 2018 at 22:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .