我看到了许多关于如何查找不在另一个表中的记录的示例，但是查找不在表2中或在表2中但freq column值小于10%的记录时遇到了很多问题。
我首先加入一个变种的列表，其中包含brca1，brca2的ensembl基因名，以及任何以brc开头的基因，其中一个变种位于起始和终止位置之间。
从这些结果中，我想检查kaviar等位基因频率（k）并返回结果，这些结果要么在kaviar表中没有条目，要么在kaviar表中alle\u freq<.10的结果。第一次连接的结果需要通过chr、pos、ref和alt与kaviar匹配。
我试过：

SELECT DISTINCT *
FROM puzz p, ensembl ens, kaviar k
WHERE (ens.gene_name IN ('BRCA1', 'BRCA2')
   OR ens.gene_name LIKE 'RAS%')
AND p.chr = ens.chromosome
AND p.pos >= ens.start AND p.pos <= ens.stop
AND NOT EXISTS
 (SELECT k.chromosome, k.pos, k.ref, k.alt, k.alle_freq, k.alle_cnt
  FROM public_hg19.kaviar k
  WHERE  p.chr = k.chromosome
  AND p.pos = k.pos
  AND p.ref = k.ref
  AND p.alt = k.alt
  )
  AND p.pos = k.pos
  AND p.ref = k.ref
  AND p.alt = k.alt
  AND k.alle_freq < .10

我也试过：

WITH puzz AS (
SELECT * 
FROM puzz p
WHERE p.gt IS NOT NULL
)

SELECT DISTINCT t1.*, kav.*
FROM
(SELECT puzz.*, ens.*
FROM puzz, public_hg19.ensembl_genes AS ens
WHERE (ens.gene_name IN IN ('BRCA1', 'BRCA2')
   OR ens.gene_name LIKE 'RAS%')
AND puzz.chr = ens.chromosome
AND puzz.pos BETWEEN ens.start AND ens.stop
AND ens.chromosome NOT LIKE "H%") t1

LEFT JOIN  

public_hg19.kaviar as kav
ON kav.chromosome = t1.chr
AND kav.pos = t1.pos
AND kav.ref = t1.ref
AND kav.alt = t1.alt
AND (kav.alle_freq < .10 OR kav.alle_freq IS NULL)

解决方案：感谢@johnbollinger为解决方案提供了框架。
因为impala不编制索引，所以最快的解决方案是创建一个临时表，缩小传递给字符串操作的行数，如enstemp表所示。

WITH ens AS (
 SELECT DISTINCT chromosome as chr, start, stop, gene_name
   FROM public_hg19.ensembl_genes
  WHERE (gene_name IN ( 'BRCA1', 'BRCA2')
     OR gene_name LIKE 'RAS%')
    AND chromosome NOT LIKE "H%"
)
SELECT p.*, k.chromosome, k.pos, k.id, k.ref, k.alt,
       k.qual, (k.alle_freq * 100) as kav_freqPct, k.alle_cnt as kav_count
  FROM
(SELECT DISTINCT p.sample_id, p.chr, p.pos, p.id,
                 p.ref, p.alt, p.qual, p.filter,
                 ens.gene_name
  FROM ens, p7_ptb.itmi_102_puzzle p
  WHERE p.chr = ens.chr
  AND p.gt IS NOT NULL
  AND p.pos >= ens.start AND p.pos <= ens.stop
) AS p
LEFT JOIN public_hg19.kaviar k
      ON p.chr = k.chromosome
      AND p.pos = k.pos
      AND p.ref = k.ref
      AND p.alt = k.alt
WHERE COALESCE(k.alle_freq, 0.0) < .10

正如@gordon linoff所指出的，下面的一行也可能是

WHERE (k.alle_freq IS NULL OR k.alle_freq < 0.10)

两个final子句返回相同的结果，但在impala上，coalesce函数的速度更快。