Drupal 8 basic query work (entityQuery)

  $nids = \Drupal::entityQuery('node')
  ->condition('type', 'my_custom_type')
  ->execute();
$nodes = \Drupal\node\Entity\Node::loadMultiple($nids);

    $connection = \Drupal::database();
    $query = $connection->query("SELECT * FROM {node} n WHERE type > :type", [
      ':type' => 'article',
    ]);
    $result = $query->fetchAll();
    foreach ($result as $item) {
      $node = \Drupal\node\Entity\Node::load($item->nid);
      //dpm($node);
      $title = $node->get('title')->getValue();
      $title = $title[0]['value']);

  $database = \Drupal::database();
  foreach ($roles as $role) {
    $query = $database->select('role_terms', 'rt');
    $query->condition('rt.uid', $userCurrentId);
    $query->condition('rt.role_name', $role, '=');
    $query->fields('rt', ['role_name']);
    $result = $query->execute();
    // $redirect_url = Url::fromUri('entity:node/42');
    $redirect_url = 'roleterms/form?role=anonymous';
  }

  With joins:
  $query = $connection->select('node', 'n', $options);
$query->join('node_field_data', 'nfd', 'n.nid = nfd.nid');
$query
  ->fields('n', ['nid'])
  ->fields('nfd', ['title'])
  ->condition('nfd.type', 'page')
  ->condition('nfd.status', '1')
  ->orderBy('nfd.created', 'DESC')
  ->addTag('node_access');

https://www.drupal.org/docs/8/api/database-api/dynamic-queries/joins