有没有办法在这种类型的SQL SELECT中删除嵌套查询？

word

phrase

word is in phrase

SELECT DISTINCT t1.id
FROM t1 
  JOIN t2
    ON t1.id = t2.t1key
  JOIN t2 copyt2
    ON copyt2.t3key = t2.t3key 
WHERE copyt2.t1key IN
  (6,4)       --what you want to check here

SELECT DISTINCT t2a.t1key
FROM t2 AS t2a
WHERE NOT EXISTS
  ( SELECT *
    FROM t2 AS t2b
    WHERE t2b.t1key IN (6,4)
      AND NOT EXISTS
      ( SELECT *
        FROM t2 AS t2c
        WHERE t2a.t3key = t2c.t3key
          AND t2c.t1key = t2b.t1key
      )
  )

SELECT a.t1key
FROM t2 AS a
  JOIN t2 as b
    ON  a.t3key = b.t3key
WHERE b.t1key IN (6,4)       --list you want to check
GROUP BY a.t1key, a.t3key
HAVING COUNT(*) = 2          --size of list
;

SELECT DISTINCT t1key
FROM t2
WHERE t3key IN
  ( SELECT t3key
    FROM t2
    WHERE t1key IN (6,4)
    GROUP BY t3key
    HAVING COUNT(*) = 2
  )
;

NON EXISTS

(2)

(2,7)

SELECT DISTINCT a2.t1key, COUNT( * ) AS cnt
FROM t2 AS a1
    LEFT JOIN t2 AS a2 ON a2.t3key = a1.t3key
WHERE a1.t1key IN ( 6, 4 ) 
GROUP BY a2.t3key, a2.t1key
HAVING cnt >=2

IN (6,4)

cnt >=2

IN

id

IN (6)

cnt >=1

>

select distinct t1key
from t2
where t3key in
(
    select t3key from t2 where t1key = 6
    intersect
    select t3key from t2 where t1key = 4
)

==> 3, 4, 6

select distinct t2b.t1key
from 
  t2 t2a
  inner join t2 t2b on t2a.t3key = t2b.t3key
where t2a.t1key in (6, 5) /* or whatever */

select distinct t2b.t1key
from 
  t2 t2a
  inner join t2 t2b on t2a.t3key = t2b.t3key and t2a.t1key = 6
  inner join t2 t2c on t2a.t3key = t2c.t3key and t2a.t1key = 5