How to avoid using a subquery for exclusion in a many-to-many sql model?

Question

Let's say we have a model with posts and tags with a many to many relationship.

For example :

Posts table

Id	Title	HtmlContent
1	Cat eating a cucumber	...
2	Chicken flying above a cucumber	...
3	Cucumber taking revenge	...

Tags table

Id	Label
1	cat
2	chicken
3	vegetables
4	funny

PostsTags association table

Id	PostId	TagId
1	1	1
1	1	3
1	1	4
2	2	2
2	2	3
2	2	4
3	3	1
3	3	3

Yesterday my objective was to find the number of Posts not involving a cat nor being funny (i.e. I don't want Posts having a connection with Tags 1 or 4).

The query I came up with was this one :

select count(p.Id)
from posts p
where p.Id not in (
    select postid 
    from poststags 
    where TagId in (1, 4)
    group by postid
)

I am wondering if there is a way to exclude posts in the many-to-many relationship without executing any subquery ?

FYI I am using an SQLite database.

Answer 1

You can use aggregation and window function COUNT():

SELECT DISTINCT COUNT(CASE WHEN SUM(tagid IN (1, 4)) = 0 THEN 1 END) OVER ()
FROM PostsTags
GROUP BY postid

Or, window function SUM():

SELECT DISTINCT SUM(SUM(tagid IN (1, 4)) = 0) OVER ()
FROM PostsTags
GROUP BY postid

See the demo.