SOQL query for getting contacts and leads by email

Question

I'm trying to write a single soql query to get all contacts and leads with a particular email. Now, I'm able to write two different queries to search for contacts and leads. But is this possible using a single query(to search on multiple objects).

The reason I'm looking for a single query is that, I'm using salesforce REST API in javascript and I don't want two separate requests for contact and lead search.

score 3 · Answer 1 · answered Apr 24 '12 at 15:36

SOQL does not allow UNION statements like SQL does, so there's no way (currently) to do this in one query.

Since you are looking to trim down your requests, you could create an Apex web service, which you could then call from your javascript code. The Apex portion would execute your two SOQL statements and then return the results. This would would still be two queries, but done within one request. I can't say for sure whether using this approach would improve performance or not, it depends on a number of factors. But it's something you could experiment with. Hope this helps.

score 2 · Accepted Answer · answered Apr 24 '12 at 16:58

2

Thanks Adam. This definitely is a way to go. But I just tried SOSL instead of SOQL and the workbench.developerforce.com helped me great deal to try out my queries.. I was able to fire a simple SOSL query to search in both Lead and Contact objects. The query is

FIND {test@email.com} IN EMAIL FIELDS RETURNING Contact, Lead

answered Apr 24 '12 at 16:58

Aravind

221
4
13

1

I didn't think of SOSL, but that would definitely be an option. Here's the documentation http://www.salesforce.com/us/developer/docs/api_rest/index_Left.htm#StartTopic=Content/resources_search.htm – Adam Butler Apr 24 '12 at 18:34
The previous solution assumed that both the contact and the lead share the same email. In the practice, the companies could have a different email (personal and professional). The Lead Sobject is a tricky one and there could be more that one way that the lead and contacts are linked in your particular org. – Juan Acosta Apr 10 '17 at 03:25

score 1 · Answer 3 · answered Apr 28 '12 at 18:26

SOSL has different limits and problems than SOQL. For this case it looks like the best option, but you should know the differences between the two. I think of using SOSL whenever I need to search in text fields from several object types. But always keep in mind the limits.

SOQL query for getting contacts and leads by email

3 Answers3