pdo - sqlite - finding if multiple records exist in table with single query

Question

I am trying to optimize an sqlite query (PHP-PDO) by having one query run instead of many queries on the same data. I have a table with a simple structure:

word
-----
aaaa
bb
cccc
dddd
ffff

now, I have a list of words aaaa, gg, ffff. For each of them, if the item exists in the table, I return 1 or else 0.

Is it possible to make this with a single query which with the aforementioned data, would return something like:

aaaa 1
gg 0
ffff 1

Thank you in advance.

Answer 1

You can do it with EXISTS:

WITH cte(word) AS (VALUES ('aaaa'), ('gg'), ('ffff'))
SELECT c.word, 
       EXISTS (SELECT 1 FROM tablename t WHERE t.word = c.word) exists_in_the_table
FROM cte c

or with a LEFT join:

WITH cte(word) AS (VALUES ('aaaa'), ('gg'), ('ffff'))
SELECT DISTINCT c.word, t.word IS NOT NULL exists_in_the_table
FROM cte c LEFT JOIN tablename t
ON t.word = c.word

If there are not duplicate words in the table you can remove DISTINCT.

See the demo.
Results:

> word | exists_in_the_table
> :--- | ------------------:
> aaaa |                   1
> gg   |                   0
> ffff |                   1