CODEWITHSUNDEEP
sqlsql-server
Ramesh M
Ramesh M

Reputation: 41

multiple NOT LIKE operator on same column with OR condition fails in SQL

Table Schema:

CREATE TABLE [dbo].[Message](
    [id] [int] NOT NULL,
    [created_on] [datetime] NULL,
    [message] [nvarchar](max) NULL,
 CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)
) ON [PRIMARY]

Values:

1   '2013-01-01 00:00:00.000'   'error occured "BASKET_BALL"'
2   '2014-01-01 00:00:00.000'   'error occured "FOOT_BALL"'
3   '2012-01-01 00:00:00.000'   'I am not involved in like operator'
4   '2014-02-01 00:00:00.000'   'I might be involved'

Query return against the table:

SELECT 
    ID,CREATED_ON,MESSAGE 
FROM 
    MESSAGE
WHERE 
    MESSAGE NOT LIKE '%"FOOT_BALL"%' OR MESSAGE NOT LIKE '%BASKET_BALL%'
    AND CREATED_ON >= '2014-01-01'

Output:

1   2013-01-01 00:00:00.000 error occured "BASKET_BALL"
2   2014-01-01 00:00:00.000 error occured "FOOT_BALL"
3   2012-01-01 00:00:00.000 I am not involved in like operator
4   2014-02-01 00:00:00.000 I might be involved

Question:

Could some one explain why the query returns all the table values when explicitly the date condition is mentioned for greater than 2014 ? I understand by keeping the message condition in bracket it yields proper result. However, like to know why the sql excludes date condition mentioned when the not like operator is not in bracket.

Upvotes: 4

Views: 8125

Answers (2)

jpw
jpw

Reputation: 44881

It's because the AND operator has higher precedence than OR. Change to this:

SELECT 
    ID,CREATED_ON,MESSAGE 
FROM 
    MESSAGE
WHERE 
    (MESSAGE NOT LIKE '%"FOOT_BALL"%' OR MESSAGE NOT LIKE '%BASKET_BALL%')
    AND CREATED_ON >= '2014-01-01'

Depending on what you want maybe the parenthesis should surround the latter part, like so:

    MESSAGE NOT LIKE '%"FOOT_BALL"%' OR (MESSAGE NOT LIKE '%BASKET_BALL%'
    AND CREATED_ON >= '2014-01-01')

Upvotes: 5

Mihai
Mihai

Reputation: 26784

You are missing parantheses so OR having lower precedence translates as

WHERE 
MESSAGE NOT LIKE '%"FOOT_BALL"%' OR (MESSAGE NOT LIKE '%BASKET_BALL%'
AND CREATED_ON >= '2014-01-01')

MESSAGE NOT LIKE '%"FOOT_BALL"%'

returs 3 rows, doesnt care about the date

Put both ORs in parentheses.

Upvotes: 1

Related Questions