I've been breaking my brains over the following problem. I have two BigQuery tables, one table with approximately 3 million search terms:

```
search_term
number of people named joe
how to paint a table black
top 100 pop songs
lovely horses
..

```

and a list of 8000 "keywords". Each row in the keyword table is a number of words.

```
keyword
name joe
horses 
baby kitten
song top 100
..

```

For each string in the 'query' table, I want to check if it contains any of the words that are in the 'keywords' table. However, the order in which the keywords appear in the 'query' string does not matter, and there can be other words inbetween. This is the resulting table I'm looking for:

```
search term                    contains_keywords
number of people named joe     TRUE
how to paint a table black     FALSE
top 100 pop songs              TRUE
lovely horses                  TRUE
..  

```

I've come up with the following code to check if each search term (as a whole) completely matches a keyword, but I have no clue how to go about splitting the keywords and then checking if each query contains these words. This is the code I have so far, but I'd greatly appreciate any help or pointers in the right direction.

```
SELECT 
  *
, CASE 
    WHEN search_term IN (
      SELECT
        keyword
      FROM 
        keywords)
    THEN true
    ELSE false
  END AS contains_keyword
FROM search_terms

```

EDIT: I've provided the two above sample tables to make it easier to try out your own code.

```

WITH 

search_terms AS (
  SELECT 'number of people named joe' AS search_term UNION ALL
  SELECT 'how to paint a table black' AS search_term UNION ALL
  SELECT 'top 100 pop songs'          AS search_term UNION ALL
  SELECT 'lovely horses'              AS search_term

  )

,keywords AS (
  SELECT 'name joe'         AS keyword UNION ALL
  SELECT 'horses lovely'    AS keyword UNION ALL  
  SELECT 'baby kitten'      AS keyword UNION ALL
  SELECT 'song top 100'     AS keyword
  )  
SELECT * FROM search_terms

```