SQL query question SELECT NOT IN

Question

I am sure making a silly mistake but I can t figure what   In SQL Server 2005 I am trying select all customers except those who have made a reservation before 2 AM   When I run this query   SELECT idCustomer FROM reservations  WHERE idCustomer NOT IN     SELECT distinct idCustomer FROM reservations     WHERE DATEPART   hour  insertDate   lt  2    I get 0 results   But   SELECT idCustomer FROM reservations    returns 152 000 results and the  NOT IN  part   SELECT distinct idCustomer FROM reservations  WHERE DATEPART   hour  insertDate   lt  2   returns only 284 rows

User · Answer

SELECT Reservations idCustomer FROM Reservations  nolock  LEFT OUTER JOIN  reservations ExcludedReservations  nolock  ON Reservations idCustomer ExcludedReservations idCustomer AND DATEPART hour  ExcludedReservations insertDate   lt  2 WHERE ExcludedReservations idCustomer IS NULL AND Reservations idCustomer IS NOT NULL GROUP BY Reservations idCustomer    Update  Added additional criteria to handle idCustomer being NULL  which was apparently the main issue the original poster had

User · Answer

SELECT MIN A maxsal  secondhigh FROM         SELECT TOP 2 MAX EmployeeBasic  maxsal       FROM M Salary       GROUP BY EmployeeBasic       ORDER BY EmployeeBasic DESC        A

User · Answer

It s always dangerous to have NULL in the IN list - it often behaves as expected for the IN but not for the NOT IN   IF 1 NOT IN  1  2  3  NULL  PRINT  1 NOT IN  1  2  3  NULL   IF 1 NOT IN  2  3  NULL  PRINT  1 NOT IN  2  3  NULL   IF 1 NOT IN  2  3  PRINT  1 NOT IN  2  3   -- Prints IF 1 IN  1  2  3  NULL  PRINT  1 IN  1  2  3  NULL   -- Prints IF 1 IN  2  3  NULL  PRINT  1 IN  2  3  NULL   IF 1 IN  2  3  PRINT  1 IN  2  3

User · Answer

Sorry if I ve missed the point  but wouldn t the following do what you want on it s own   SELECT distinct idCustomer FROM reservations  WHERE DATEPART hour  insertDate   gt   2

User · Answer

select   from table name where id 5 and column name not in   sandy  pandy

User · Answer

SELECT distinct idCustomer FROM reservations WHERE DATEPART   hour  insertDate   lt  2   and idCustomer is not null   Make sure your list parameter does not contain null values   Here s an explanation   WHERE field1 NOT IN  1  2  3  null    is the same as   WHERE NOT  field1   1 OR field1   2 OR field1   3 OR field1   null     That last comparision evaluates to null  That null is OR d with the rest of the boolean expression  yielding null      null is negated  yielding null  null is not true - the where clause only keeps true rows  so all rows are filtered        Edit   this explanation is pretty good  but I wish to address one thing to stave off future nit-picking    TRUE OR NULL  would evaluate to TRUE   This is relevant if field1   3  for example   That TRUE value would be negated to FALSE and the row would be filtered

User · Answer

Sorry if I ve missed the point  but wouldn t the following do what you want on it s own   SELECT distinct idCustomer FROM reservations  WHERE DATEPART hour  insertDate   gt   2

User · Answer

SELECT distinct idCustomer FROM reservations WHERE DATEPART   hour  insertDate   lt  2   and idCustomer is not null   Make sure your list parameter does not contain null values   Here s an explanation   WHERE field1 NOT IN  1  2  3  null    is the same as   WHERE NOT  field1   1 OR field1   2 OR field1   3 OR field1   null     That last comparision evaluates to null  That null is OR d with the rest of the boolean expression  yielding null      null is negated  yielding null  null is not true - the where clause only keeps true rows  so all rows are filtered        Edit   this explanation is pretty good  but I wish to address one thing to stave off future nit-picking    TRUE OR NULL  would evaluate to TRUE   This is relevant if field1   3  for example   That TRUE value would be negated to FALSE and the row would be filtered

User · Answer

Given it s SQL 2005  you can also try this It s similar to Oracle s MINUS command  opposite of UNION   But I would also suggest adding the DATEPART   hour  insertDate  column for debug  SELECT idCustomer FROM reservations  EXCEPT SELECT idCustomer FROM reservations WHERE DATEPART   hour  insertDate   lt  2

User · Answer

Given it s SQL 2005  you can also try this It s similar to Oracle s MINUS command  opposite of UNION   But I would also suggest adding the DATEPART   hour  insertDate  column for debug  SELECT idCustomer FROM reservations  EXCEPT SELECT idCustomer FROM reservations WHERE DATEPART   hour  insertDate   lt  2

User · Answer

SELECT distinct idCustomer FROM reservations WHERE DATEPART   hour  insertDate   lt  2   and idCustomer is not null   Make sure your list parameter does not contain null values   Here s an explanation   WHERE field1 NOT IN  1  2  3  null    is the same as   WHERE NOT  field1   1 OR field1   2 OR field1   3 OR field1   null     That last comparision evaluates to null  That null is OR d with the rest of the boolean expression  yielding null      null is negated  yielding null  null is not true - the where clause only keeps true rows  so all rows are filtered        Edit   this explanation is pretty good  but I wish to address one thing to stave off future nit-picking    TRUE OR NULL  would evaluate to TRUE   This is relevant if field1   3  for example   That TRUE value would be negated to FALSE and the row would be filtered

User · Answer

It s always dangerous to have NULL in the IN list - it often behaves as expected for the IN but not for the NOT IN   IF 1 NOT IN  1  2  3  NULL  PRINT  1 NOT IN  1  2  3  NULL   IF 1 NOT IN  2  3  NULL  PRINT  1 NOT IN  2  3  NULL   IF 1 NOT IN  2  3  PRINT  1 NOT IN  2  3   -- Prints IF 1 IN  1  2  3  NULL  PRINT  1 IN  1  2  3  NULL   -- Prints IF 1 IN  2  3  NULL  PRINT  1 IN  2  3  NULL   IF 1 IN  2  3  PRINT  1 IN  2  3

User · Answer

SELECT Reservations idCustomer FROM Reservations  nolock  LEFT OUTER JOIN  reservations ExcludedReservations  nolock  ON Reservations idCustomer ExcludedReservations idCustomer AND DATEPART hour  ExcludedReservations insertDate   lt  2 WHERE ExcludedReservations idCustomer IS NULL AND Reservations idCustomer IS NOT NULL GROUP BY Reservations idCustomer    Update  Added additional criteria to handle idCustomer being NULL  which was apparently the main issue the original poster had

User · Answer

select   from table name where id 5 and column name not in   sandy  pandy

User · Answer

Given it s SQL 2005  you can also try this It s similar to Oracle s MINUS command  opposite of UNION   But I would also suggest adding the DATEPART   hour  insertDate  column for debug  SELECT idCustomer FROM reservations  EXCEPT SELECT idCustomer FROM reservations WHERE DATEPART   hour  insertDate   lt  2

User · Answer

SELECT Reservations idCustomer FROM Reservations  nolock  LEFT OUTER JOIN  reservations ExcludedReservations  nolock  ON Reservations idCustomer ExcludedReservations idCustomer AND DATEPART hour  ExcludedReservations insertDate   lt  2 WHERE ExcludedReservations idCustomer IS NULL AND Reservations idCustomer IS NOT NULL GROUP BY Reservations idCustomer    Update  Added additional criteria to handle idCustomer being NULL  which was apparently the main issue the original poster had

User · Answer

SELECT Reservations idCustomer FROM Reservations  nolock  LEFT OUTER JOIN  reservations ExcludedReservations  nolock  ON Reservations idCustomer ExcludedReservations idCustomer AND DATEPART hour  ExcludedReservations insertDate   lt  2 WHERE ExcludedReservations idCustomer IS NULL AND Reservations idCustomer IS NOT NULL GROUP BY Reservations idCustomer    Update  Added additional criteria to handle idCustomer being NULL  which was apparently the main issue the original poster had

User · Answer

It s always dangerous to have NULL in the IN list - it often behaves as expected for the IN but not for the NOT IN   IF 1 NOT IN  1  2  3  NULL  PRINT  1 NOT IN  1  2  3  NULL   IF 1 NOT IN  2  3  NULL  PRINT  1 NOT IN  2  3  NULL   IF 1 NOT IN  2  3  PRINT  1 NOT IN  2  3   -- Prints IF 1 IN  1  2  3  NULL  PRINT  1 IN  1  2  3  NULL   -- Prints IF 1 IN  2  3  NULL  PRINT  1 IN  2  3  NULL   IF 1 IN  2  3  PRINT  1 IN  2  3

User · Answer

It s always dangerous to have NULL in the IN list - it often behaves as expected for the IN but not for the NOT IN   IF 1 NOT IN  1  2  3  NULL  PRINT  1 NOT IN  1  2  3  NULL   IF 1 NOT IN  2  3  NULL  PRINT  1 NOT IN  2  3  NULL   IF 1 NOT IN  2  3  PRINT  1 NOT IN  2  3   -- Prints IF 1 IN  1  2  3  NULL  PRINT  1 IN  1  2  3  NULL   -- Prints IF 1 IN  2  3  NULL  PRINT  1 IN  2  3  NULL   IF 1 IN  2  3  PRINT  1 IN  2  3

User · Answer

Sorry if I ve missed the point  but wouldn t the following do what you want on it s own   SELECT distinct idCustomer FROM reservations  WHERE DATEPART hour  insertDate   gt   2

User · Answer

SELECT distinct idCustomer FROM reservations WHERE DATEPART   hour  insertDate   lt  2   and idCustomer is not null   Make sure your list parameter does not contain null values   Here s an explanation   WHERE field1 NOT IN  1  2  3  null    is the same as   WHERE NOT  field1   1 OR field1   2 OR field1   3 OR field1   null     That last comparision evaluates to null  That null is OR d with the rest of the boolean expression  yielding null      null is negated  yielding null  null is not true - the where clause only keeps true rows  so all rows are filtered        Edit   this explanation is pretty good  but I wish to address one thing to stave off future nit-picking    TRUE OR NULL  would evaluate to TRUE   This is relevant if field1   3  for example   That TRUE value would be negated to FALSE and the row would be filtered

User · Answer

Given it s SQL 2005  you can also try this It s similar to Oracle s MINUS command  opposite of UNION   But I would also suggest adding the DATEPART   hour  insertDate  column for debug  SELECT idCustomer FROM reservations  EXCEPT SELECT idCustomer FROM reservations WHERE DATEPART   hour  insertDate   lt  2

User · Answer

Sorry if I ve missed the point  but wouldn t the following do what you want on it s own   SELECT distinct idCustomer FROM reservations  WHERE DATEPART hour  insertDate   gt   2

User · Answer

SELECT MIN A maxsal  secondhigh FROM         SELECT TOP 2 MAX EmployeeBasic  maxsal       FROM M Salary       GROUP BY EmployeeBasic       ORDER BY EmployeeBasic DESC        A

[sql] SQL query question: SELECT ... NOT IN

Examples related to sql

Examples related to sql-server