SOQL query to JOIN all Product2s with their corresponding PricebookEntry in the standard price book

Question

I'm trying to send some SOQL in the REST API, in order to JOIN all entries from Product2 with their UnitPrice value in the corresponding PricebookEntry in the standard price book.

Either a LEFT JOIN or INNER JOIN could possibly work for my scenario. – Here's my naive attempt:

SELECT p.Id, 
       p.Description, 
       p.IsActive, 
       p.Name, 
       p.ProductCode, 
       (SELECT Product2Id, 
               UnitPrice 
        FROM   PricebookEntry pe) 
FROM   Product2 p 
WHERE  pe.Product2Id =: p.Id 

The above query fails with:

'bind variables only allowed in Apex code'

What's the part that I'm missing? Is this really possible only in Apex?

Answer 1

There is no concept of INNER JOIN or OUTER JOIN. Instead, there are SEMI JOIN and ANTI JOIN in SOQL.

The query you are looking for is this one:

SELECT p.Id, 
       p.Description, 
       p.IsActive, 
       p.Name, 
       p.ProductCode, 
       (SELECT Product2Id, 
               UnitPrice 
        FROM   PricebookEntries) 
FROM   Product2 p