Drupal - entityQuery to get Nodes referenced by other nodes like an SQL JOIN

You can use 'entity' field query

$query = \Drupal::entityQuery('node');
$query->condition('status', 1);
$query->condition('type', 'article');
$query->condition('field_some_field.entity:node.title', 'Referenced node title');

I just had exactly the same problem with a Drupal 8 instance, and while I wasn't able to solve it with a single query as I'd've liked to, I did succeed with the following:

My goal was to get all the nodes of type A or B which meet certain criteria, one of which is that they are referenced by type C. (I've removed that other stuff here to focus on the issue.) Type has an entity reference field which allows multiple entries to either type A or B.

First we get all the nids that are referenced:

$connection = \Drupal::database();
$query = $connection->query("SELECT DISTINCT field_name_target_id  FROM {node__field_name}");
$referenced_nids = $query->fetchCol();

Note that you'll need to adjust your code to match the name of your reference field. See https://www.drupal.org/docs/8/api/database-api/static-queries, which is where I ripped that bit from.

Then my query to get the actual nodes/nids:

$storage = \Drupal::entityTypeManager()->getStorage('node');

$query = $storage->getQuery();

$nids = $query->condition('type', ['TypeA', 'TypeB'], 'IN')
  ->condition('status', 1)
  ->condition('nid', $referenced_nids, 'IN')
  ->sort('title')
  ->execute();

UPDATE: I learned today that the first step of my procedure is not considered proper by Drupal standards, as you shouldn't make direct queries on Drupal tables (see https://www.drupal.org/core/d8-bc-policy#schema). The following method is more brute force unfortunately, but caching the results should help.

Instead I refactored the first part using an EntityQuery.

// Get all the entities of type C, the referencing entity:
$query = \Drupal::entityQuery('node')
  ->condition('type', 'TypeC')
  ->condition('status', 1);
$typeC_nids = $query->execute();
$typeC_nodes = $storage->loadMultiple($typec_nids);

// Loop through the nodes and get all the references:
$referenced_nids = [];
foreach ($typeC_nodes as $node) {
 $references = array_column($node->field_name->getValue(), 'target_id');
 foreach ($references as $reference) {
   if (!in_array($reference, $referenced_nids)) {
     $referenced_nids[] = $reference;
   }
 }

}

Then use that list of the referenced NIDs in the second query.