Confusion Over OR and And Operators

• SQLisAwesome

  SSC-Addicted

  Points: 428

  More actions

  December 16, 2016 at 11:42 am

  #1918468

  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');

  

• MMartin1

  One Orange Chip

  Points: 27795

  More actions

  December 16, 2016 at 2:26 pm

  #1918497

  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

  One Orange Chip

  Points: 27795

  More actions

  December 16, 2016 at 2:32 pm

  #1918498

  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.

  ----------------------------------------------------