Стояла задача по деактивации всех элементов каталога, которые активны, которые есть в наличии (таблица b_catalog_product), и у которых цена равна 0, либо отсутствует (таблица b_catalog_price).

Естественно это можно было сделать олдскульными методами и по циклу спрашивать у товара, какая у тебя цена и количество. Либо заранее построить массивы-конфигураторы с информацией по каждой табличке и по циклу делать проверку.

А можно это сделать одним запросом, пусть и не самым простым, заджоинить остальные таблицы к таблице элементов.

Итак, объявляем пространства имен, которые будем использовать.

use Bitrix\Main\Loader;
use Bitrix\Catalog\ProductTable;
use Bitrix\Catalog\PriceTable;
use Bitrix\Main\Entity\Query;
use Bitrix\Iblock\ElementTable;


далее подключаем модули, которые участвуют в выборке


Loader::includeModule('iblock');
Loader::includeModule('sale');
Loader::includeModule('catalog');


далее идет само построение запроса:


$query = ElementTable::query();


здесь все понятно
$query->where('ACTIVE', 'Y');

здесь добавляем таблицу b_catalog_product, даем ей алиас PRODUCTS
$query->registerRuntimeField(
 'PRODUCTS',
  new \Bitrix\Main\Entity\ReferenceField(
  'PRODUCTS',
  ProductTable::class,
  ['=this.ID' => 'ref.ID']
 )
);

здесь добавляем таблицу b_catalog_price, даем ей алиас PRICES
$query->registerRuntimeField(
 'PRICES',
  new \Bitrix\Main\Entity\ReferenceField(
  'PRICES',
  PriceTable::class,
   ['=this.ID' => 'ref.PRODUCT_ID']
  )
);

далее идет построение сложной логики
$query->where(Query::filter()
 ->logic('or')
 ->where(Query::filter()
  ->logic('and')->where('PRICES.PRICE', 0)->where('PRICES.CATALOG_GROUP_ID', 1)
)
 ->where(Query::filter()
  ->logic('and')->whereNull('PRICES.PRODUCT_ID')->whereNull('PRICES.CATALOG_GROUP_ID')
 )
);

смысл в том, что запись в таблице существует и цена равна 0, либо записи еще не существует, т.е. товар еще не сохранялся с ценами. left join  вернет пустые значения, нам это подходит.

еще одно условие
$query->where('PRODUCTS.QUANTITY', '>', 0);

далее передаем селект
$query->setSelect([
  'ID',
 'NAME',
 'ACTIVE',
 'PRICE' => 'PRICES.PRICE',
 'CATALOG_GROUP_ID' => 'PRICES.CATALOG_GROUP_ID',
 'PRICES_PRODUCT_ID' => 'PRICES.PRODUCT_ID',
 'QUANTITY' => 'PRODUCTS.QUANTITY',
 'PRODUCTS_PRODUCT_ID' => 'PRODUCTS.ID',
]);

выполняем запрос
$db_res = $query->exec();

далее в привычной нам форме while-ом проходим результат выборки.
Задача выполнена!

Хочу выразить благодарность Александру Шубину в помощи решения данной задачи.
Вот ссылка на его блог.

Предыдущий вариант выглядел таким образом:

$query = new Query( ProductTable::getEntity() );

$db_res = $query
 ->setSelect(['ID','QUANTITY'])
 ->registerRuntimeField('PRICE', [
  'data_type' => PriceTable::getEntity(),
  'reference' => [
   '=this.ID' => 'ref.PRODUCT_ID'
  ]
 ]
)
 ->setFilter(['>QUANTITY' => 0])
 ->addFilter('PRICE.CATALOG_GROUP_ID', [false, 1] )
 ->addSelect('PRICE.PRODUCT_ID','PRODUCT_ID')
 ->addSelect('PRICE.PRICE','PRICE')
 ->addSelect('PRICE.CATALOG_GROUP_ID','CATALOG_GROUP_ID')
 ->registerRuntimeField('ELEMENT', [
  'data_type' => ElementTable::getEntity(),
  'reference' => [
   '=this.ID' => 'ref.ID'
   ]
  ]
)
 ->addSelect('ELEMENT.ID','ELEMENT_ID')
 ->addSelect('ELEMENT.ACTIVE','ACTIVE')
 ->addSelect('ELEMENT.NAME','NAME')
 ->addFilter('ELEMENT.ACTIVE' , 'Y')
 ->exec();

но в этом случае не работал фильтр по CATALOG_GROUP_ID, строка
->addFilter('PRICE.CATALOG_GROUP_ID', [false, 1] )

в каких вариациях я только не пробовал это делать.
Хотя ->addFilter('ELEMENT.ACTIVE' , 'Y') отрабатывает как нужно.
Времени на разбор не было, поэтому все было переписано, как указано выше.

Всем терпения и хорошего, читаемого кода!

Полезная ссылка здесь.