/* Code to accompnay Ron Talmage's "The NOT IN() Subquery Trap" article
*/

/*See for yourself*/
SELECT pub_id FROM Publishers
   WHERE pub_id NOT IN 
      (SELECT pub_id FROM Titles)

/*  insert a single NULL value in the pub_id column */
BEGIN TRANSACTION
PRINT '------- Before -------'
SELECT pub_id FROM Publishers
   WHERE pub_id NOT IN 
      (SELECT pub_id FROM Titles)
UPDATE Titles
SET pub_id = NULL
   WHERE title_id = 'BU2075'
PRINT '------- After -------'
SELECT pub_id FROM Publishers
   WHERE pub_id NOT IN 
      (SELECT pub_id FROM Titles)
ROLLBACK TRANSACTION

SELECT pub_id FROM Publishers
   WHERE pub_id NOT IN ('1389', '0736', '0877', NULL)

/* To evaluate an IN() function, we can translate it into a series of OR statements.*/
SELECT pub_id FROM Publishers
   WHERE pub_id IN ('1389', '0736', '0877')
becomes, using OR,  
SELECT pub_id FROM Publishers
   WHERE (pub_id = '1389' OR pub_id = '0736' 
      OR pub_id = '0877')

/* To evaluate the NOT IN(list) subquery, we follow the same rules, just adding the NOT:*/
SELECT pub_id FROM Publishers
   WHERE pub_id NOT IN ('1389', '0736', '0877')
/* becomes, using OR,*/
SELECT pub_id FROM Publishers
   WHERE NOT (pub_id = '1389' OR pub_id = '0736' 
      OR pub_id = '0877')
/* But this time, we can distribute the NOT into the grouping, changing OR to AND:*/
SELECT pub_id FROM Publishers
   WHERE (pub_id != '1389' AND pub_id != '0736' 
      AND pub_id != '0877')

/* Now what happens when we add a NULL value to the list? */
SELECT pub_id FROM Publishers
   WHERE pub_id NOT IN ('1389', '0736', '0877', NULL)
/* becomes */
SELECT pub_id FROM Publishers
   WHERE NOT(pub_id = '1389' OR pub_id = '0736' 
      OR pub_id = '0877' OR pub_id = NULL)
/* and then */
SELECT pub_id FROM Publishers
   WHERE (pub_id != '1389' AND pub_id != '0736' 
      AND pub_id != '0877' AND pub_id != NULL)

/* try is isql/w */
SET ANSI_NULLS ON
GO
DECLARE @x int 
SELECT @x = NULL
IF @x = NULL
   PRINT '@x=null is true'
ELSE	
   IF NOT @x = NULL
      PRINT '@x=null is false'
   ELSE
      PRINT '@x=null is unknown'

/* Let's try the IN(list) subquery again*/

SET ANSI_NULLS ON
GO
   SELECT pub_id FROM Publishers
      WHERE pub_id NOT IN ('1389', '0736', '0877', NULL)
/* one solution */
SELECT pub_id FROM Publishers
   WHERE pub_id NOT IN 
      (SELECT pub_id FROM Titles
         WHERE pub_id IS NOT NULL)

/* A second, and perhaps better way, is to rewrite the query using NOT EXISTS, correlating the subquery to the outer table: */
SELECT p.pub_id FROM Publishers p
   WHERE NOT EXISTS 
      (SELECT * FROM Titles t
         WHERE t.pub_id = p.pub_id)



