Soql put inside where clause not working, but works alone

Question

I am trying the below SOQL, for some reason it doesn't work. I tried the SOQL inside where clause alone that works,

select Id,Name from Offer__c where Id in (select Offer_Id__c from Segment_Offers__c where Segment_Id__c ='a2n17000000EhO7AAK' order by Offer_Priority__c ASC LIMIT 4)

Answer 1

SOQL is not the same as SQL and does not support much in the way of expressions in the right-hand side of a "fieldExpression". It does, as Derek F points out in the comments, have some semi-join and anti-join capability using "IN (SELECT ...)" or "NOT IN (SELECT ...). However, this only works in specific scenarios.

Some options you have here are:

1. To execute the queries separately and generate a binding variable to substitute the Id match options.
2. To change the query to select the data a different way.
3. To ensure you align with supported SOQL semi-join capabilities

In this case, applying option 2, perhaps you could use:

SELECT Offer_Id__c, Offer_Id__r.Name
    FROM Segment_Offers__c
    WHERE Segment_Id__c = :id
    ORDER BY Offer_Priority__c ASC LIMIT 4

(Always best to use a binding for a value like an ID and to obtain that dynamically rather than hard-coding the value, which can change from org to org.)

In this case you simply need to fetch the name values through the returned Offer_Id__r.Name and either Offer_Id__c or Offer_Id__r.Id values.