Confusion Over OR and And Operators
-
If there are no nulls in the data, your query will work fine.
However, if there are any nulls in the country field, it will ignore that in your results. Try the below.
DECLARE @customers TABLE (Country VARCHAR(2))INSERT INTO @customers(Country)
VALUES('UK'),('US'),('FR'),(NULL)
--YOUR RESULTS
SELECT * from @customers where country != 'US' AND country !='UK';
Select * from @customers where Not(country = 'US' OR country ='UK');
--USING ISNULL
SELECT * from @customers where ISNULL(country,'') != 'US' AND ISNULL(country,'') !='UK';
Select * from @customers where Not(ISNULL(country,'') = 'US' OR ISNULL(country,'') ='UK');
-
Given a large table;
a more potentially SARGable approach, and readable from my perspective is
Declare @customers table (country varchar(2))
Insert into @customers(country)
values('UK'),('US'),('FR'),(NULL)
Select*
from@customers
Except
Select*
from@customers
wherecountry in ('US','UK')
;
----------------------------------------------------
-
MMartin1 (12/16/2016)
Given a large table;a more potentially SARGable approach, and readable from my perspective is
Declare @customers table (country varchar(2))
Insert into @customers(country)
values('UK'),('US'),('FR'),(NULL)
Select*
from@customers
Except
Select*
from@customers
wherecountry in ('US','UK')
;
I should note that the EXCEPT operator removes duplicates
Try
Declare @customers table (country varchar(2))
Insert into @customers(country)
values('UK'),('US'),('FR'),(NULL),('UK'),('FR')
Select*
from@customers
Except
Select*
from@customers
wherecountry in ('US','UK')
;
'FR' gets listed just once in the results.
----------------------------------------------------
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply