mysql. How to check if a list exists in the database?

Question

SQL

Good afternoon everyone, for the second day I have been trying to make a request, but I can’t figure it out, I ask for your help)
I have a database in which there are a million rows (one word each), there is a list (outside the database) that consists of a thousand rows (in one word each).
Is it possible to create ONE query that will check all the words from the list for presence in the database at once, and let's remove the rest?

For example, in the database I have the following lines:
"one"
"two"
"three"
"four"
"five"
"six"
In the list:
"one"
"five"
"seven"
"eight"
It is necessary that after the request I have only "

Answer 1

You can first collect a list of all the words to be found using UNION ALL, and then join it with the main table using RIGHT JOIN:

SELECT tt.word from q862089 t
RIGHT JOIN (
  SELECT 'one' AS word 
  UNION ALL SELECT 'five' 
  UNION ALL SELECT 'seven'
  UNION ALL SELECT 'eight'
) tt ON t.word = tt.word
WHERE t.word IS NULL;

In the example, q862089 is the name of the main table that contains all the words; word - the field that contains the word.
An alternative solution is through a temporary table in which all the searched words are added:

create temporary table tmp (word VARCHAR(64));
insert into tmp (word) VALUES ("one"), ("five"), ("six"), ('seven'), ('eight');

SELECT tt.word from q862089 t
RIGHT JOIN tmp tt ON t.word = tt.word
WHERE t.word IS NULL;

DROP TABLE tmp;

There are several requests, but perhaps it will do.