SELECT RAW a.number
FROM `my_bk` AS a
LEFT JOIN `my_bk` AS b ON meta(a).id = b.check_id AND b.type = 'bbb'
WHERE
a.type = 'aaa' AND
a.name = 'name' AND
a.class = 1 AND b IS MISSING
Select number that exists in A and does not exist in B.
WITH b AS (SELECT check_id FROM `my_bk` b1 WHERE type = 'bbb')
SELECT a.`number`
FROM `my_bk` a LEFT OUTER JOIN b ON meta(a).id = b.check_id
WHERE a.type = 'aaa'
AND a.name = 'name'
AND a.class = 1
AND b IS MISSING
;
Or:
WITH b AS (SELECT check_id FROM `my_bk` b1 WHERE type = 'bbb')
SELECT a.`number`
FROM `my_bk` a
WHERE a.type = 'aaa'
AND a.name = 'name'
AND a.class = 1
AND meta(a).id NOT IN (SELECT RAW b.check_id FROM b b2)
;
SELECT d.`number`
FROM ( SELECT a.`number`, b.check_id
FROM `my_bk` a
LEFT OUTER JOIN b ON meta(a).id = b.check_id AND b.type = "bbb"
WHERE a.type = 'aaa'
AND a.name = 'name'
AND a.class = 1) AS d
WHERE d.check_id IS MISSING ;
OR
WITH b AS (SELECT DISTINCT RAW check_id FROM `my_bk` b1 WHERE type = 'bbb')
SELECT a.`number`
FROM `my_bk` a
WHERE a.type = 'aaa' AND a.name = 'name' AND a.class = 1 AND meta(a).id NOT IN b;
OR
The following two are same (except syntax) see any faster. NOTE: Output as ARRAY.
SELECT RAW ARRAY_AGG(CASE WHEN b.check_id IS MISSING THEN a.`number` END)
FROM `my_bk` a
LEFT OUTER JOIN b ON meta(a).id = b.check_id AND b.type = "bbb"
WHERE a.type = 'aaa' AND a.name = 'name' AND a.class = 1;
OR
SELECT RAW ARRAY_AGG(a.`number`) FILTER (WHERE b.check_id IS MISSING)
FROM `my_bk` a
LEFT OUTER JOIN b ON meta(a).id = b.check_id AND b.type = "bbb"
WHERE a.type = 'aaa' AND a.name = 'name' AND a.class = 1;
CREATE INDEX adv_name_type_class_number ON my_bk(name,class,number) WHERE (type=“aaa”);
CREATE INDEX adv_type_check_id ON my_bk(check_id) WHERE (type=“bbb”);