/* IN example from http://idealprogrammer.com 
 PURPOSE: IN returns true or false indicating whether value is found in list or 
          subselect
 
 
 SYNTAX: test_expression [ NOT ] IN 
        ( subquery | expression [ ,...n ]
        ) 
 
 
 SELECT [ ALL | DISTINCT ] 
			[TOP ( expression ) [PERCENT] [ WITH TIES ] ] 
			 column1 AS displayname1, displayname2 = column_name2, column_name3 displayname3  
			[ INTO new_table ] 
			[ FROM { table_source } [ ,...n ] ] 
			[ WHERE  test_expression [ NOT ] IN 
				 ( subquery | expression [ ,...n ] ) ] 
			[ GROUP BY ] 
			[ HAVING  search_condition  ] 
			[ ORDER BY  order_expression [ ASC | DESC ] ] 
*/
 
 
CREATE TABLE people(
     ID          int,
     firstname   varchar (20),
     lastname    varchar (20),
     statecode   varchar (2),
     alive       bit,
     height      int )
 GO
 
CREATE TABLE addressbook(
     ID          INT,
     firstname   VARCHAR (20),
     lastname    VARCHAR (20),
     email   VARCHAR (50))
 
 GO
 
 
 
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height) 
 VALUES (1,  'Paul', 'Revere', 'AL', 0, 72)
 GO
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height) 
 VALUES (2,  'John', 'Lennon', 'NY', 0, 69)
 GO
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height)
 VALUES (3,  'Steve', 'Martin', 'NY', 1, 75)
 GO
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height)
 VALUES (4,  'George', 'Washington', 'VA', 0, 75)
 GO
 
 
 INSERT INTO addressbook (ID, firstname, lastname, email) 
 VALUES (1,  'Paul', 'Revere', 'paul@email.com')
 GO
 
 INSERT INTO addressbook (ID, firstname, lastname, email)
 VALUES (2,  'Wade', 'Harvey', 'wade@email.com')
 GO
 
 INSERT INTO addressbook (ID, firstname, lastname, email)
 VALUES (3,  'Jack', 'Powers', 'jack@email.com')
 GO
 
 INSERT INTO addressbook (ID, firstname, lastname, email)
 VALUES (4,  'George', 'Washington', 'george@email.com')
 GO
 
 -- 1. Using IN with list
 SELECT 'Example #1' as Example, statecode, height   
  FROM people  
  WHERE firstname IN ('George', 'Wade')
 GO
 
-- 2. Using IN with subquery
 SELECT 'EXAMPLE #2', *
  FROM people p  
  WHERE p.firstname IN
  (SELECT a.firstname FROM addressbook a)
 
 GO
 
 -- 3. Using NOT IN with subquery
 SELECT 'EXAMPLE #3', *
  FROM people p  
  WHERE p.firstname NOT IN
  (SELECT a.firstname FROM addressbook a)
 
 GO
 
 
 
 
 DROP TABLE people;
 
 GO
 
 DROP TABLE addressbook;
 
 GO

/* IN example from http://idealprogrammer.com PURPOSE: IN returns true or false indicating whether value is found in list or subselect SYNTAX: test_expression [ NOT ] IN ( subquery | expression [ ,...n ] ) SELECT [ ALL | DISTINCT ] [TOP ( expression ) [PERCENT] [ WITH TIES ] ] column1 AS displayname1, displayname2 = column_name2, column_name3 displayname3 [ INTO new_table ] [ FROM { table_source } [ ,...n ] ] [ WHERE test_expression [ NOT ] IN ( subquery | expression [ ,...n ] ) ] [ GROUP BY ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ] */ CREATE TABLE people( ID int, firstname varchar (20), lastname varchar (20), statecode varchar (2), alive bit, height int ) GO CREATE TABLE addressbook( ID INT, firstname VARCHAR (20), lastname VARCHAR (20), email VARCHAR (50)) GO INSERT INTO people (ID, firstname, lastname, statecode, alive, height) VALUES (1, 'Paul', 'Revere', 'AL', 0, 72) GO INSERT INTO people (ID, firstname, lastname, statecode, alive, height) VALUES (2, 'John', 'Lennon', 'NY', 0, 69) GO INSERT INTO people (ID, firstname, lastname, statecode, alive, height) VALUES (3, 'Steve', 'Martin', 'NY', 1, 75) GO INSERT INTO people (ID, firstname, lastname, statecode, alive, height) VALUES (4, 'George', 'Washington', 'VA', 0, 75) GO INSERT INTO addressbook (ID, firstname, lastname, email) VALUES (1, 'Paul', 'Revere', 'paul@email.com') GO INSERT INTO addressbook (ID, firstname, lastname, email) VALUES (2, 'Wade', 'Harvey', 'wade@email.com') GO INSERT INTO addressbook (ID, firstname, lastname, email) VALUES (3, 'Jack', 'Powers', 'jack@email.com') GO INSERT INTO addressbook (ID, firstname, lastname, email) VALUES (4, 'George', 'Washington', 'george@email.com') GO -- 1. Using IN with list SELECT 'Example #1' as Example, statecode, height FROM people WHERE firstname IN ('George', 'Wade') GO -- 2. Using IN with subquery SELECT 'EXAMPLE #2', * FROM people p WHERE p.firstname IN (SELECT a.firstname FROM addressbook a) GO -- 3. Using NOT IN with subquery SELECT 'EXAMPLE #3', * FROM people p WHERE p.firstname NOT IN (SELECT a.firstname FROM addressbook a) GO DROP TABLE people; GO DROP TABLE addressbook; GO