I have a table with one word each row and a table with some text in a row. I need to select from the second table only those rows that does not contain words from the first table.

For example:

Table with constratint words

Table with text

Nothing should be selected in this case, because every row in the second table contains words from the first table.

I only have an idea to use CROSS JOIN to achive this

SELECT DISTINCT text FROM text_table CROSS JOIN words_table
           WHERE CONTAINS(text, constraint_word ) = 0

Is there a way to do it without using CROSS JOIN?

CodePudding user response：

I believe this works (I think the issue with the CROSS JOIN route is that it includes any texts that don't contain at least one of the words--not just texts that don't contain any):

SELECT DISTINCT text FROM text_table WHERE (SELECT COUNT(*) FROM words_table WHERE CONTAINS(text, constraint_word)) = 0;

CodePudding user response：

Your idea is fine, since you need to test all words for each text. This is what CROSS JOIN does - a combination.

Additionally you need to filter all text records, where there are no matches at all. This means the count of non-matches over all combinations per text is maximum (= number of constraint_words, here 6). This filter can be done using GROUP BY WITH HAVING

-- text without any constaint_word
SELECT t.text, count(*)
FROM text_table t
JOIN words_table w ON CONTAINS(t.text, w.constraint_word, 1) = 0
GROUP BY t.text
HAVING count(*) = (SELECT count(*) FROM words_table)
;

Try the demo on on SQL Fiddle

See also

• Use string contains function in oracle SQL query
• How does contains() in PL-SQL work?
• Oracle context indexes
• Creating and Maintaining Oracle Text Indexes

CodePudding user response：

contains means Oracle Text; cross join means Cartesian product (usually performance nightmare).

One option which avoids both of these is instr function (which checks existence of the constraint_word in text, but this time using inner join) and the minus set operator.

Something like this, using sample data you posted:

SQL> select * from text_table;

TEXT
---------------------------
word1.apple; word3, example
word1, apple, word2.car
word1 word2 orange word3
mushroomword1 word2 word3
word1 car
qwerty

6 rows selected.

SQL> select * From words_table;

CONSTRAI
--------
example
apple
orange
mushroom
car
qwerty

6 rows selected.

SQL>

As you said, initially query shouldn't return anything because all constraint_words exist in text:

SQL> select c.text
  2  from text_table c
  3  minus
  4  select b.text
  5  from words_table a join text_table b on instr(b.text, a.constraint_word) > 0;

no rows selected

Let's modify one of text rows:

SQL> update text_table set text = 'xxx' where text = 'qwerty';

1 row updated.

What's the result now?

SQL> select c.text
  2  from text_table c
  3  minus
  4  select b.text
  5  from words_table a join text_table b on instr(b.text, a.constraint_word) > 0;

TEXT
---------------------------
xxx

SQL>

Right; text we've just modified.