June 3, 2017 at 12:49 pm
Guys,
Maybe most of your guys it is Simple question and it is simple question but I am
a little bit confuses here. I have data in SQL something like this
Aud_ID
412
554
610
1002
What I want to exclude all records where Audit_ID start with 4 and 5
I used
Where (Aud_ID not like '4%' or Aud_ID not like '5%')
but It didn't work. Then I change it to
Where (Aud_ID not like '4%' AND Aud_ID not like '5%)
it did work.
My understanding was it should work with OR. Is any can explain in the past experience why
it didn't work with OR.
I know I can google it, but I want to hear from past personal exp.
Thank You.
June 3, 2017 at 1:15 pm
rocky_498 - Saturday, June 3, 2017 12:49 PMGuys,Maybe most of your guys it is Simple question and it is simple question but I am
a little bit confuses here. I have data in SQL something like this
Aud_ID
412
554
610
1002
What I want to exclude all records where Audit_ID start with 4 and 5I used
Where (Aud_ID not like '4%' or Aud_ID not like '5%')
but It didn't work. Then I change it to
Where (Aud_ID not like '4%' AND Aud_ID not like '5%)
it did work.My understanding was it should work with OR. Is any can explain in the past experience why
it didn't work with OR.I know I can google it, but I want to hear from past personal exp.
Thank You.
If Aud_ID is an integer, then you shouldn't be using string values in the WHERE clause. You'd have to use something like this.
WHERE SUBSTRING(CONVERT(Varchar(9), Aud_ID), 1, 1) NOT IN ('4', '5')
If Aud_ID is a varchar, then this will do what you want:WHERE SUBSTRING(Aud_ID, 1, 1) NOT IN ('4', '5')
Performance is going to suck for either one of these statements because your requirements dictate that the entire column is read to be able to process the WHERE clause predicate.
You might be better off creating a persisted computed column to store the first character of Aud_ID. If excluding anything that starts with a 4 or a 5 is something that's normally excluded from standard reports, then you could make the persisted computed column a bit and store a 0 or 1 to determine if the value is included or not. If not or if there are more standard search criteria than just 4 or 5, then just store the first character. Whichever way you go, you can created a nonclustered index on it and then use that column in your WHERE clause instead.
I have to ask if this is just an exercise or if you're really filtering based on hard-coded primary or foreign key values.
June 3, 2017 at 2:29 pm
To directly answer your question, it should not work with OR. Let's step through the logic 🙂
"X OR Y" evaluates TRUE in the following cases:
1) Only X is true
2) Only Y is true
3) Both X and Y are true.
In your case, X is "Aud_ID NOT LIKE '4%'" and Y is "Aud_ID NOT LIKE '5%'".
Think about how this will work for '412'.
X will be false, because '412' IS like '4%'. So far so good.
However, Y will be true, because '412' is not like '5%'
Since Y is true, the OR evaluates to true, and the row is returned.
Looking at this another way, for an OR to exclude rows, both sides must evaluate to false (ignoring NULLs for a second).
In this case, for X to be false would mean the string starts with '4', and for Y to be false would mean the string starts with '5'. Since those two things can't obtain at the same time, the only rows this would exclude would be rows where Aud_ID is NULL.
EDIT:
As a further quick point, if the column is one of the character data types, you could actually avoid scanning the whole table by making the WHERE something like this:
WHERE Aud_ID<'4' OR Aud_ID>='6'
That should allow a seek (ORs often prevent seeks, but in this particular case it doesn't in my tests; if it does in your case you could also try the trick of doing a UNION ALL on two queries that each use one of the predicates, which should then be able to use seeks).
Now, whether that's worth it will depend on how much of your table you're excluding, among other things. If this query is returning almost all of the table, a scan might be better than a seek anyway, so definitely test.
In theory you could do something like this for an integer column also, but it would be a pretty unwieldy query.
Cheers!
June 6, 2017 at 3:23 pm
Look up De Morgan's laws in any book on logic.
Please post DDL and follow ANSI/ISO standards when asking for help.
June 7, 2017 at 12:21 am
The where condition with "OR" in this case would negate each other as it is with the same column, that's why you have to use "AND" instead of "OR".
June 7, 2017 at 2:51 am
rocky_498 - Saturday, June 3, 2017 12:49 PMGuys,Maybe most of your guys it is Simple question and it is simple question but I am
a little bit confuses here. I have data in SQL something like this
Aud_ID
412
554
610
1002
What I want to exclude all records where Audit_ID start with 4 and 5I used
Where (Aud_ID not like '4%' or Aud_ID not like '5%')
but It didn't work. Then I change it to
Where (Aud_ID not like '4%' AND Aud_ID not like '5%)
it did work.My understanding was it should work with OR. Is any can explain in the past experience why
it didn't work with OR.I know I can google it, but I want to hear from past personal exp.
Thank You.
If you insist on using OR, you can double-negate (that's a term I just made up) your WHERE clause. These statements are equivalent:
SELECT *
FROM master.sys.columns
WHERE name NOT LIKE 'i%' AND name NOT LIKE 'n%'
SELECT *
FROM master.sys.columns
WHERE NOT (name LIKE 'i%' OR name LIKE 'n%')
Ed Wagner - Saturday, June 3, 2017 1:15 PMWHERE SUBSTRING(CONVERT(Varchar(9), Aud_ID), 1, 1) NOT IN ('4', '5')If Aud_ID is a varchar, then this will do what you want:
WHERE SUBSTRING(Aud_ID, 1, 1) NOT IN ('4', '5')Performance is going to suck for either one of these statements because your requirements dictate that the entire column is read to be able to process the WHERE clause predicate.
Assuming the data is uniformly distributed, you may be returning about 80% of the data in the column, and so you're likely to need an index scan in any case. To give yourself any chance at all of an index seek, you need to use the sargable LIKE instead of SUBSTRING.
John
June 7, 2017 at 7:52 am
rocky_498 - Saturday, June 3, 2017 12:49 PMGuys,Maybe most of your guys it is Simple question and it is simple question but I am
a little bit confuses here. I have data in SQL something like this
Aud_ID
412
554
610
1002
What I want to exclude all records where Audit_ID start with 4 and 5I used
Where (Aud_ID not like '4%' or Aud_ID not like '5%')
but It didn't work. Then I change it to
Where (Aud_ID not like '4%' AND Aud_ID not like '5%)
it did work.My understanding was it should work with OR. Is any can explain in the past experience why
it didn't work with OR.I know I can google it, but I want to hear from past personal exp.
Thank You.
AND is basically a continuation of filtering the result set, while OR is more like 2 queries.
So AND removes 4% first and then 5% from the remaining results.
While OR removes 4% while leaving 5% in the first result set, then removes 5% while leaving 4% in the second set, then combines the results.
June 7, 2017 at 6:07 pm
If the data is character, skip the OR and the AND completely, and use WHERE Aud_ID NOT LIKE '[4,5]%'
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply