Problem with IN and NOT IN

Question

hey guys , I wrote this query with IN and NOT IN , but I received the same answer

 

IF '16101769' IN (SELECT DISTINCT Outlet FROM current_authorization )
	PRINT 'TRUE'
ELSE
	PRINT 'FALSE'

 

 

IF '16101769' NOT IN (SELECT DISTINCT Outlet FROM current_authorization )
	PRINT 'TRUE'
ELSE
	PRINT 'FALSE'

In both cases I receive FALSE . why is that ? it whether exists or not .

I looked the execution plan , Sql Engine implements it with a semi left join  with this condition :

 

Outlet IS NULL OR Outlet = '16101769'

I know I can solve this problem with IF EXISTS , I'm not looking for a solution ,

 

I'm looking for the reason . can anyone help me ?

 

---

Siavash Tadayon Nia - Iran , Tehran - Tel : [phone]

Answer 1

Hi,
I checked this getting different output.

DECLARE @TestX TABLE
(Val INT)

INSERT INTO @TestX
SELECT 16101769

IF '16101769' IN (SELECT DISTINCT Val FROM @TestX )
	PRINT 'TRUE'
ELSE
    PRINT 'FALSE'

---

Shatrughna.

Answer 2

Hi  Sam !
 
Lets try to learn from below example;

USE AdventureWorks
GO

IF '289' IN (SELECT DISTINCT EmployeeID FROM HumanResources.Employee)
 PRINT 'TRUE'
ELSE
 PRINT 'FALSE'
--TRUE
USE AdventureWorks
GO

IF '289' NOT IN (SELECT DISTINCT EmployeeID FROM HumanResources.Employee)
 PRINT 'TRUE'
ELSE
 PRINT 'FALSE'
--FALSE

I would prefer you to use CASE Expressionunlike using IF ESLE like doing VB Programming in
MS SQL Server. We have CASE Expression in MS SQL Server and thats what Data Developers used.

SELECT EmployeeID , CASE WHEN EmployeeID IN ('289') THEN 'TRUE' ELSE 'FALSE' END FROM HumanResources.Employee
WHERE CASE WHEN EmployeeID IN ('289') THEN 'TRUE' ELSE 'FALSE' END  = 'TRUE'

It would be better if you explain your requirement so we could suggest you more better solution.

 
Please let me know if this doesn’t work for you. Hope I have answered you correctly.
 
Thanks, Hasham

 

Answer 3

Is it possible that Outlet column contains NULLs in current_authorization table?

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 4

My best guess is that it is NULL values in the current_authorization table that are giving you confusing results.

You cannot compare a value to a NULL value.

Try chaging your query to

IF '16101769' NOT IN (SELECT DISTINCT Outlet FROM current_authorization WHERE Outlet IS NOT NULL)
PRINT 'TRUE'
ELSE
PRINT 'FALSE'

This should give you the results you are looking for.

 

HTH

Paul Livengood

---

Paul Livengood

Answer 5

The NOT IN operator can be translated into a series of "non-equal" expressions used together with AND logical operators, e.g.:

...column1 NOT IN ('a', 'b', 'c')

can be written as:

...column1 <> 'a' AND column1 <> 'b' AND column1 <> 'c'

Therefore, whenever NULL value occurs as one of the values in parenthesis you get no rows (NULL compared to any value always returns false).

To avoid such situations you can simply filter out NULLs:

IF '16101769' NOT IN (SELECT DISTINCT Outlet FROM current_authorization WHERE Outlet IS NOT NULL)...

---

Pawel Potasinski, SQL Server MVP My blog: http://sqlgeek.pl

Answer 6

Another alternative is to use a NOT EXISTS comparator rather than NOT IN:

If NOT EXISTS(select outlet from current_Authorization where outlet = '16101769')

This will help to avoid the NULL issues and the 3-state logic problem that goes with the NOT IN clause.  This issue can be generally avoided by avoiding the NOT IN operator and rather using the NOT EXISTS operator.

Answer 7

There is a good blog explaining the problem with IN/NOT IN in details:

Why you should never use IN/NOT IN in SQL

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog