Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

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

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...