Using NULL in 'IN' Statement

• ramkaay

  SSC Enthusiast

  Points: 136

  More actions

  March 29, 2012 at 10:59 pm

  #255706

  hi,

  I've table as below:

  Table A

  ---------

  Column A Column B

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

  0 1001

  1 1001

  null 1001

  0 1002

  1 1002

  My aim is to select value of Colum B by which Column A has values 0, 1 and null.

  My query is like this below:

  select distinct column B from Table A

  where Column A is null

  and column A in(0,1)

  My aim is to select only the value 1001 from Table A based on the condition.

  How can i achieve this?

  Please let me know.

  Regards,

  Ram

• pietlinden

  SSC Guru

  Points: 63494

  More actions

  March 29, 2012 at 11:18 pm

  #1466742

  Is this a homework assignment?

  WHERE SomeColumn IS NULL

  OR SomeColumn IN (1,2);

  is how I would do it, but then I never tried putting NULL in a list as if it were a constant, because it isn't.

  When you tried it, what happened? (Feel free to learn from your mistakes. Everybody here does it. Test out some assumptions. Some will be found to be true, others false. Welcome to learning.)

• Latheesh NK

  SSCertifiable

  Points: 6559

  More actions

  March 29, 2012 at 11:30 pm

  #1466746

  It would fetch all the values who has either of 0,1 or NULL. Why do you want to achieve here. Why cant you have the ColumnB = the value that you are looking for?

• Wildcat

  SSCarpal Tunnel

  Points: 4774

  More actions

  March 29, 2012 at 11:43 pm

  #1466748

  sqlzealot-81 (3/29/2012)

  ---

  It would fetch all the values who has either of 0,1 or NULL. Why do you want to achieve here. Why cant you have the ColumnB = the value that you are looking for?

  I guess OP wants to get ColumnB when ColumnA = 1 and ColumnA = 2 and ColumnA IS NULL. 😎

  Use self join.....

• raj acharya

  SSCrazy

  Points: 2805

  More actions

  March 30, 2012 at 1:00 am

  #1466765

  Hey, i think you can simplify this by adding default constraint where value is null or the other way.

  but yeah for your problem i have two quick solutions hope this is helpful .

  and also added more data for columnb.

  create table #temp (columna int ,columnb int )

  insert into #temp

  select 0, 1001 union all

  select 1, 1001 union all

  select null ,1001 union all

  select 0, 1002 union all

  select 1, 1002 union all

  select 0, 1003 union all

  select 1, 1003 union all

  select null, 1003

  /* solution 1*/

  select a.* from #temp a inner join #temp b

  on a.columnb = b.columnb

  where isnull(a.columna,0) in (0,1)

  and b.columna is null

  /* solution 1*/

  /* solution 2*/

  select columnb from #temp where columna is null

  and columnb in (select columnb from #temp where columna in (0,1))

  /* solution 2*/

  --set ansi_nulls on

  drop table #temp

  Raj Acharya