I have a many to many relationship with 3 objects Position, Candidate, Job Application(Junction Object). I wanted to retrieve all the positions applied by a particular candidate and also the positions not applied by any candidate using one query. Is it possible using semi joins and anti joins? Or I have to create separate lists and then combine them into third list.
You need 2 Queries because you cannot query using or clause in a subquery. In Soql it is either include specific child records or exclude child records but not both. I have used sub queries in the select clause and then used when in other records as a subquery but it would not work in this situation because you want to exclude records that you need.
I do want to share this blog. I think it gives a great explanation of what can be done with Soql.
Yes it should be possible. I believe you are limited to two semi- or anti-joins in a single query, but you only need two. Making some assumptions about API Names, a query looking something like the below should work:
SELECT Id FROM Position__c WHERE Id IN ( SELECT Position__c FROM Job_Application__c WHERE Candidate__c = 'xyz' ) OR Id NOT IN ( SELECT Position__c FROM Job_Application__c )
You might also be limited to one join on a specific table in one query, I'm not sure but will investigate and update this answer.