December 10, 2016 at 1:40 pm
Suppose I want to find customers in the customers table who are not in the US or the UK.
To me the query for the above requirements would be as follows:
Select * from customers where country != 'US' AND country !='UK';
However, I've seen people write the query for the above requirements as follows:
Select * from customers where Not(country = 'US' OR country ='UK');
Please explain which one is correct, thanks.
December 10, 2016 at 1:51 pm
SQLUSERMAN (12/10/2016)
Suppose I want to find customers in the customers table who are not in the US or the UK.To me the query for the above requirements would be as follows:
Select * from customers where country != 'US' AND country !='UK';
However, I've seen people write the query for the above requirements as follows:
Select * from customers where Not(country = 'US' OR ='UK');
Please explain which one is correct, thanks.
Generally, if it works then its "correct" but in this case you should just simplify the query and use NOT IN
😎
where country NOT IN ('US','UK');
December 10, 2016 at 2:04 pm
Hi, thank you so much for your reply. I'm still not a hundred percent clear on the differences between the OR and the AND operators.
What if I want to find all customers who are neither in the US nor have a credit rating above 600. Should I use the AND or the OR operator for this situation?
The two queries below yield different results and I'm not sure which one is correct.
select * from customers where not(country = 'US' or credit_rating>600);
select * from customers where country != 'US' and credit_rating<=600);
December 10, 2016 at 4:03 pm
SQLUSERMAN (12/10/2016)
Hi, thank you so much for your reply. I'm still not a hundred percent clear on the differences between the OR and the AND operators.What if I want to find all customers who are neither in the US nor have a credit rating above 600. Should I use the AND or the OR operator for this situation?
The two queries below yield different results and I'm not sure which one is correct.
select * from customers where not(country = 'US' or credit_rating>600);
select * from customers where country != 'US' and credit_rating<=600);
The difference is that all the ANDed conditions must be met for a row to be returned. Any of the ORed conditions must be met for a row to be returned.
Your second query above wouldn't run because of the mismatched parentheses at the end. I'd write it this way to return the rows from customers that are not in the US and the credit rating is 600 or less.
SELECT *
FROM dbo.customers
WHERE NOT country 'US'
AND credit_rating <= 600;
December 10, 2016 at 4:26 pm
Hi, thanks for replying. The mismatched parenthesis is just a copy paste error. I copied and pasted the first query then tried to modify but forgot to remove the parenthesis.
December 10, 2016 at 8:39 pm
Try to keep it as simple as possible, not only is the logic more readable, it also helps the performance.
😎
December 12, 2016 at 10:54 am
Get a book on logic and read the section on DeMorgan's Laws. But be careful! SQL has three value logic (true, false, unknown), and things do not always work the way you would expect.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
December 12, 2016 at 1:45 pm
SQLUSERMAN (12/10/2016)
Suppose I want to find customers in the customers table who are not in the US or the UK.To me the query for the above requirements would be as follows:
Select * from customers where country != 'US' AND country !='UK';
However, I've seen people write the query for the above requirements as follows:
Select * from customers where Not(country = 'US' OR country ='UK');
Please explain which one is correct, thanks.
These two queries are logically equivalent. Use whichever one makes more sense to you.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 14, 2016 at 1:01 pm
I find the easiest way to think of it is to write the WHERE clause to select the records i DON'T want and then wrap those rules in a NOT(...)
So if you want everyone who outside the US and everyone with a credit rating <=600, including those who are in the US
Choose everyone in the US AND Everyone with a credit rating > 600 and then invert the selection by forcing the NOT.
SELECT ... FROM ... WHERE NOT( Country='US' AND CreditRating > 600)
December 14, 2016 at 1:18 pm
drew.allen (12/12/2016)
SQLUSERMAN (12/10/2016)
Suppose I want to find customers in the customers table who are not in the US or the UK.To me the query for the above requirements would be as follows:
Select * from customers where country != 'US' AND country !='UK';
However, I've seen people write the query for the above requirements as follows:
Select * from customers where Not(country = 'US' OR country ='UK');
Please explain which one is correct, thanks.
These two queries are logically equivalent. Use whichever one makes more sense to you.
Drew
As long as no NULLS are involved. In the OP's actual case two different columns were on either side of the operator. All sorts of fun occurs if either one of those are NULL
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 14, 2016 at 3:21 pm
Matt Miller (#4) (12/14/2016)
drew.allen (12/12/2016)
SQLUSERMAN (12/10/2016)
Suppose I want to find customers in the customers table who are not in the US or the UK.To me the query for the above requirements would be as follows:
Select * from customers where country != 'US' AND country !='UK';
However, I've seen people write the query for the above requirements as follows:
Select * from customers where Not(country = 'US' OR country ='UK');
Please explain which one is correct, thanks.
These two queries are logically equivalent. Use whichever one makes more sense to you.
Drew
As long as no NULLS are involved. In the OP's actual case two different columns were on either side of the operator. All sorts of fun occurs if either one of those are NULL
They are logically equivalent. PERIOD. NULLs will affect both calculations in the same way.
There may be a difference in SARGability however. I haven't had time to set up a test for this.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 15, 2016 at 12:39 pm
drew.allen (12/14/2016)
Matt Miller (#4) (12/14/2016)
drew.allen (12/12/2016)
SQLUSERMAN (12/10/2016)
Suppose I want to find customers in the customers table who are not in the US or the UK.To me the query for the above requirements would be as follows:
Select * from customers where country != 'US' AND country !='UK';
However, I've seen people write the query for the above requirements as follows:
Select * from customers where Not(country = 'US' OR country ='UK');
Please explain which one is correct, thanks.
These two queries are logically equivalent. Use whichever one makes more sense to you.
Drew
As long as no NULLS are involved. In the OP's actual case two different columns were on either side of the operator. All sorts of fun occurs if either one of those are NULL
They are logically equivalent. PERIOD. NULLs will affect both calculations in the same way.
There may be a difference in SARGability however. I haven't had time to set up a test for this.
Drew
Point taken but it is still important to consider if the column <country> is nullable. If it is I dont want to make a decision for the user in terms of having those nulls filtered. I want to give everything not in US and UK only and avoid assumptions.
Simple illustration
Declare @countries table(country char(2))
;
Insertinto @countries
values ('US'), ('UK'), (NULL),('FR'),('SP'),('BG')
;
Select*
from@countries
where(Not(country = 'US' OR country ='UK'))
--OR (country IS NULL) /* OP, uncomment to see the difference. The business person receiving this report may want to see this row as well. */
;
----------------------------------------------------
December 16, 2016 at 2:23 am
1st Example:
Select * from customers where country != 'US' AND country !='UK'; --Works
Select * from customers where Not(country = 'US' OR country ='UK'); --Works because criteria within the NOT construct all apply to same field and are therefor mutually exclusive
2nd Example:
Select * from customers where country != 'US' and credit_rating<=600); --Works
Select * from customers where not(country = 'US' or credit_rating>600); --Excludes more rows than you're expecting in this case because criteria within the NOT construct apply to multiple fields, and are therefor not mutually exclusive
🙂
December 16, 2016 at 5:33 am
simon.oakes (12/16/2016)
1st Example:Select * from customers where country != 'US' AND country !='UK'; --Works
Select * from customers where Not(country = 'US' OR country ='UK'); --Works because criteria within the NOT construct all apply to same field and are therefor mutually exclusive
2nd Example:
Select * from customers where country != 'US' and credit_rating<=600); --Works
Select * from customers where not(country = 'US' or credit_rating>600); --Excludes more rows than you're expecting in this case because criteria within the NOT construct apply to multiple fields, and are therefor not mutually exclusive
🙂
Can you augment this dataset to demonstrate?
SELECT * FROM (VALUES
('UK', 0),('UK', 600),('UK', 700),('UK', NULL),
('US', 0),('US', 600),('US', 700),('US', NULL),
(NULL, 0),(NULL, 600),(NULL, 700),(NULL, NULL),
('ES', 0),('ES', 600),('ES', 700),('ES', NULL)
) d (Country, credit_rating)
--WHERE country != 'US' AND country !='UK'; -- 4 rows
--WHERE Not(country = 'US' OR country ='UK'); -- 4 rows
--WHERE country != 'US' and credit_rating <= 600; -- 4 rows
WHERE not(country = 'US' or credit_rating > 600); -- 4 rows
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 16, 2016 at 9:53 am
simon.oakes (12/16/2016)
1st Example:Select * from customers where country != 'US' AND country !='UK'; --Works
Select * from customers where Not(country = 'US' OR country ='UK'); --Works because criteria within the NOT construct all apply to same field and are therefor mutually exclusive
2nd Example:
Select * from customers where country != 'US' and credit_rating<=600); --Works
Select * from customers where not(country = 'US' or credit_rating>600); --Excludes more rows than you're expecting in this case because criteria within the NOT construct apply to multiple fields, and are therefor not mutually exclusive
🙂
This is a simple application of DeMorgan's Laws. Remember the lack of exclusivity applies to both formulations.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply