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:
- To execute the queries separately and generate a binding variable to substitute the Id match options.
- To change the query to select the data a different way.
- 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
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