Combination AND OR result
I have a table like this.
NoteID CustomerID CustomerName Note Type Date Active
6 81 Paris test Info 2015-06-04 1
10 81 Rotterdam Everything is allright Comment 2015-06-04 1
11 81 Hamburg Everything is allright Info 2015-06-04 1
12 81 Hamburg Everything is allright Info 2015-06-04 1
13 81 Amsterdam Everything is allright Info 2015-06-04 1
14 81 Rotterdam Everything is allLeft Comment 2015-06-04 1
15 81 Hamburg Everything is allLeft Info 2015-06-04 1
16 81 Hamburg Everything is allLeft Info 2015-06-04 1
17 81 Amsterdam Everything is allLeft Info 2015-06-04 1
When I execute this request:
SELECT *
FROM CarsNote
WHERE Note LIKE '%ddddddddddddddd%'
AND Type != 'Comment'
OR Type != 'error'
As a result, all the notes.
I expect there are no notes as a result. Because of the instructions LIKE
'%ddddddddddddddd%'
.
Can someone please explain why this query is not working as I expected?
source to share
Sure. He referred to the operator's priority (or more generally, the order in which an operator is evaluated).
If you add parentheses this is what you are evaluating:
WHERE ((Note LIKE '%ddddddddddddddd%') AND Type != 'Comment') OR Type != 'error'
According to MSDN:
When more than one logical operator is used in a statement, NOT is evaluated first, then AND, and finally OR. Arithmetic and bitwise operators are processed before logical operators.
The part NOT
is great - this means you can get rid of unnecessary parentheses. The rest is a little more complicated - AND
will always take precedence over OR
, all others are equal. It also means that Tim's second suggestion will actually work, but yeah, don't. This is just crazy.
Even if you knew about the rules for evaluating an operatior, it's a bad idea not to make it explicit - it's just too fragile, let alone hard to read (I'm currently working with a codebase full of such things - just not. You will save yourself and everyone else in the future.). Just use this instead:
where Note like '%ddddddddddddddd%' or not (Type = 'Comment' or Type = 'Error')
or even better,
where Note like '%ddddddddddddddd%' and Type not in ('Comment', 'Error')
source to share
You have two options:
- wrap
OR
in palette - repeat
AND
+OR
First approach (*):
SELECT *
FROM CarsNote
WHERE Note LIKE '%ddddddddddddddd%'
AND (Type != 'Comment' OR Type != 'error')
Secondly:
SELECT *
FROM CarsNote
WHERE Note LIKE '%ddddddddddddddd%' AND Type != 'Comment'
OR Note LIKE '%ddddddddddddddd%' AND Type != 'error'
I prefer the former as it is more concise and less error prone.
* Important note: Both approaches are pointless, since the combination of !=
and is OR
always correct, it removes the filter and returns all records. So, you have to use AND
:
WHERE Note LIKE '%ddddddddddddddd%'
AND (Type != 'Comment' AND Type != 'error')
you don't need parathesesis with AND
:
WHERE Note LIKE '%ddddddddddddddd%'
AND Type != 'Comment' AND Type != 'error'
If you want to include / exclude multiple values, it is more readable to use IN
/ NOT IN
:
WHERE Note LIKE '%ddddddddddddddd%'
AND Type NOT IN('Comment', 'error')
Note that this skips records where Type
- NULL
. Therefore, you must use:
WHERE Note LIKE '%ddddddddddddddd%'
AND (Type IS NULL OR Type NOT IN('Comment', 'error'))
source to share
What are you talking about with this SQL:
Show me all entries with comments of type d ... d, not type "Comment" OR where type is not "error"
The AND operator is executed before OR. It can be written as:
(Note LIKE '%ddddddddddddddd%' AND Type != 'Comment') OR Type != 'error'
I think you will need:
Note Like '%ddddddddddddddd%' AND (Type != 'Comment' OR Type != 'error')
source to share