sqlite - Retrieve Record Based on a Value Existing in Record's Association Table

Question

I have two tables. posts is the record I want, whilst post_tags allows a one-to-many relationship between them. NB. I have this working, however, they do not feel efficient to me and I could do with some SQLite gurus to help me out.

posts
id

Answer 1

Usually EXISTS performs better:

SELECT p.* 
FROM posts p 
WHERE EXISTS (SELECT 1 FROM tag t WHERE t.post_id = p.id AND t.tag IN ('tag1', 'tag2'))

Or you could do it with an INNER JOIN:

SELECT DISTINCT p.*
FROM posts p INNER JOIN tag t
ON t.post_id = p.id 
WHERE t.tag IN ('tag1', 'tag2')