SQL : IN operator vs multiple ORs

There is a behaviour I would like to understand for good.

Query #1:

SELECT count(id) FROM table WHERE message like '%TEXT1%'   

Output : 504

Query #2

SELECT count(distinct id) FROM table WHERE message like '%TEXT2%'   

Output : 87

Query #3

SELECT count(distinct id) FROM table WHERE message in ('%TEXT1%','%TEXT2%' )

Output : 0

I want to understand why am I getting zero in the third query. Based on this, the ( , ) is equivalent to a multiple OR. Isn’t this OR inclusive ?

Answer

the ( , ) is equivalent to a multiple OR. Isn’t this OR inclusive ?

Sure, it’s inclusive. But it’s still an equality comparison, with no wildcard matching. It’s like writing

WHERE (message = '%TEXT1%' or message = '%TEXT2%')

rather than

WHERE (message LIKE '%TEXT1%' or message LIKE '%TEXT2%')