SQLite查询返回0结果，如何使用AND同时满足两个条件获取结果？

Hey there! Let's break down why your query isn't returning results when using AND for two conditions, and how to fix it.

First, let's cover the most common pitfall here: if you're trying to match two different values in the same column (like finding posts tagged both "python" and "sqlite" from a tag association table), writing something like this won't work:

SELECT * FROM post_tags WHERE tag = 'python' AND tag = 'sqlite';

A single row can't have a tag column equal to two different values at once—this condition will always evaluate to false, hence no results.

Here are the two most reliable fixes for this scenario: #

1. Use GROUP BY + HAVING to match multiple tags

This approach filters rows that have either tag, then groups them by the parent record (like post_id) and ensures the group has both tags:

SELECT post_id
FROM post_tags
WHERE tag IN ('python', 'sqlite')
GROUP BY post_id
HAVING COUNT(DISTINCT tag) = 2;

• The WHERE clause narrows down rows to only the tags we care about.
• GROUP BY post_id groups all tags for each post together.
• HAVING COUNT(DISTINCT tag) = 2 ensures the post has both of our target tags (use COUNT(*) instead if you know there are no duplicate tags per post).

2. Use EXISTS subqueries to verify each condition separately

If you need to pull full details from the parent table (like posts), this method checks that each condition is satisfied in a separate subquery:

SELECT p.*
FROM posts p
WHERE EXISTS (
    SELECT 1 FROM post_tags pt 
    WHERE pt.post_id = p.id AND pt.tag = 'python'
)
AND EXISTS (
    SELECT 1 FROM post_tags pt 
    WHERE pt.post_id = p.id AND pt.tag = 'sqlite'
);

Each EXISTS checks that a matching tag record exists for the post, so combining them with AND ensures both conditions are met.

If you're filtering different columns (not the same column): #

If your query is using AND on different columns (e.g., WHERE age > 18 AND country = 'USA') and still returning no results, double-check these things:

• Are your column names spelled correctly? SQLite is case-insensitive for identifiers, but typos still happen.
• Are data types matching? For example, don't forget single quotes around string values (country = USA will fail, but country = 'USA' works).
• Are there NULL values in the columns? A NULL value compared with any condition using AND will make the whole condition evaluate to unknown, so those rows won't be included. Use IS NOT NULL if needed.

内容的提问来源于stack exchange，提问作者Andreas B