JOIN subquery as right table in Views
It worked (in views plugin).

// create subquery using database api
$sub_query = \Drupal::database()-&gt;select('node__field_event_date', 'n');
    $sub_query-&gt;addField('n', 'entity_id');
    $sub_query-&gt;addExpression("MIN(n.field_event_date_value)", 'EarliestEventDate');
    $sub_query-&gt;groupBy("n.entity_id");

// create join
$join_definition = [
      'table formula' =&gt; $sub_query,
      'field' =&gt; 'entity_id',
      'left_table' =&gt; 'node_field_data',
      'left_field' =&gt; 'nid',
      'adjust' =&gt; TRUE,
    ];
$join = $this-&gt;joinManager-&gt;createInstance('standard', $join_definition);
$this-&gt;query-&gt;addRelationship('event_date', $join, 'node_field_data');


Edit:
If you do the same thing in hook_query_alter, change the last two lines like this.

$join = Drupal::service('plugin.manager.views.join')-&gt;createInstance('standard', $join_definition);
$query-&gt;addRelationship('event_date', $join, 'node_field_data');