Salesforce SOQL & SOSL

TYPEOF :

TYPEOF is an optional clause that can be used in a SELECT statement of a SOQL query when you’re querying data that contains polymorphic relationships
If you need to query multiple polymorphic relationship fields, you can use more than one TYPEOF expression in a single SELECT statement
TYPEOF is only allowed in the SELECT clause of a query. You can filter on the object type of a polymorphic relationship using the Type qualifier in a WHERE clause SELECT Id FROM Event WHERE What.Type IN ('Account', 'Opportunity')
TYPEOF isn’t allowed in queries that don’t return objects, such as COUNT()
TYPEOF can’t be used in SOQL used in Bulk API.
TYPEOF expressions can’t be nested.
TYPEOF isn’t allowed in the SELECT clause of a semi-join query. You can use TYPEOF in the SELECT clause of an outer query that contains semi-join queries

  SELECT 
      TYPEOF What
          WHEN Account THEN Phone
          ELSE Name
      END
  FROM Event
  WHERE CreatedById IN
      (
      SELECT CreatedById
      FROM Case
      )

WHERE:

ID field Semi-Join

You can include a semi-join in a WHERE clause. For example, the following query returns account IDs if an associated opportunity is lost:

SELECT Id, Name 
FROM Account 
WHERE Id IN 
  ( SELECT AccountId
    FROM Opportunity
    WHERE StageName = 'Closed Lost' 
  )

ID field Anti-Join

The following query returns account IDs for all accounts that don’t have any open opportunities:

SELECT Id 
FROM Account 
WHERE Id NOT IN
  (
    SELECT AccountId
    FROM Opportunity
    WHERE IsClosed = false
  )

Multiple Semi-Joins or Anti-Joins

You can combine semi-join or anti-join clauses in a query. For example, the following query returns account IDs that have open opportunities if the last name of the contact associated with the account is like the last name “Apple”:

SELECT Id, Name 
FROM Account
WHERE Id IN
  (
    SELECT AccountId
    FROM Contact
    WHERE LastName LIKE 'apple%'
  )
  AND Id IN
  (
    SELECT AccountId
    FROM Opportunity
    WHERE isClosed = false
  )

Note: You can use at most two subqueries in a single semi-join or anti-join query

Semi-Joins or Anti-Joins Evaluating Relationship Queries

You can create a semi-join or anti-join that evaluates a relationship quer y in a SELECT clause. For example, the following query returns opportunity IDs and their related line items if the opportunity's line item total value is more than $10,000:

SELECT Id, (SELECT Id from OpportunityLineItems) 
FROM Opportunity 
WHERE Id IN
  (
    SELECT OpportunityId
    FROM OpportunityLineItem
    WHERE totalPrice > 10000
  )

Understanding Relationship Query Limitations:

No more than 55 child-to-parent relationships can be specified in a query

A single query of polymorphic fields can count multiple times against the child-to-parent relationship limit. For example, this query has 3 relationships :😊

 SELECT 
   TYPEOF What
     WHEN Account THEN Phone, NumberOfEmployees
     WHEN Opportunity THEN Amount, CloseDate
     ELSE Name, Email
   END
 FROM Event

no more than five levels can be specified in a child-to-parent relationship. For example, Contact.Account.Owner.FirstName (three levels).
In API version 57.0 and earlier, only two levels of parent-to-child relationship can be specified in a query.
In API version 58.0 and later, up to five levels of parent-to-child relationship can be queried via REST and SOAP query calls for standard and custom objects. NOTE ℹ️ : SOQL queries with five level parent-to-child relationships aren't supported for big objects, external objects, or in Apex, Bulk API, and Bulk API 2.0.
A subquery that involves external objects or a filter on parent external objects can fetch up to 1,000 rows of data.
parent-child relationship subqueries (aggregate queries in code below) do not count against the governor limit of 100 SOQLs (synchronous limit). If the synchronous limit of the total number of SOQL queries issued is 100, the limit for subqueries is 300