SQL : IN operator vs multiple ORs

Question

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 ?

score 2 · Answer 1 · answered Mar 21 '20 at 21:46

2

IN does not take wildcards. They are specific to LIKE.

So, you need to use:

WHERE message like '%TEXT1%'  OR message like '%TEST2%'

Or, you can use regular expressions:

WHERE message ~ 'TEXT1|TEXT2'

answered Mar 21 '20 at 21:46

Gordon Linoff

1,242,037
58
646
786

Okay, can you please indicate me the documentation that specifies this ? – devio Mar 21 '20 at 21:47
1

@occulti: https://www.postgresql.org/docs/current/functions-subquery.html#FUNCTIONS-SUBQUERY-IN – Mar 21 '20 at 22:22

score 2 · Answer 2 · edited Jun 20 '20 at 09:12

2

IN checks if the value on its left-hand side is equal to any of the values in the list. It does not support pattern matching.

This behavior is standard ANSI SQL, and is also described in Postgres documentation:

expression IN (value [, ...])

The right-hand side is a parenthesized list of scalar expressions. The result is “true” if the left-hand expression's result is equal to any of the right-hand expressions. This is a shorthand notation for:

expression = value1 OR expression = value2 OR ...

So if you want to match against several possible patterns, you need OR:

where message like '%TEXT1%' or message like '%TEST2%'

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 21 '20 at 21:46

GMB

216,147
25
84
135

Okay, can you please indicate me the documentation that specifies this ? – devio Mar 21 '20 at 21:48
@occulti: this is SQL standard. I added a link to Postgres documentation in my answer. – GMB Mar 21 '20 at 21:51

score 2 · Accepted Answer · answered Mar 21 '20 at 21:50

2

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%')

answered Mar 21 '20 at 21:50

Joel Coehoorn

399,467
113
570
794

2

In PostgreSQL we could also use [Is there a combination of “LIKE” and “IN” in SQL?](https://stackoverflow.com/a/52264937/5070879) - `WHERE message LIKE ANY '{"%TEXT1%", "%TEXT2%"'}` – Lukasz Szozda Mar 21 '20 at 22:05

SQL : IN operator vs multiple ORs

3 Answers3