How to add an extra value to IN clause in mysql

Question

I have an In clause with query inside. I want to add 'NULL' to that IN. How can i add.

Here is my query

WHERE `e`.`lead_id` IN (
    select lds.lead_id 
        from mortgage_lead_leads lds 
        where lds.loan_officer_id=60
)

which gives 10 records. I want to add another record to that In clause. Can anyone help me out.

If I am not mistaken then you merely need to add `union all` and the second select to get the "extra value": http://www.w3schools.com/sql/sql_union.asp or http://www.techonthenet.com/mysql/union_all.php — Ralph, Mar 10 '16 at 20:10

score 0 · Answer 1 · answered Mar 10 '16 at 20:03

You can use a unio with select null from dual

  WHERE `e`.`lead_id` IN 
  (select lds.lead_id from mortgage_lead_leads lds 
    where lds.loan_officer_id=60 
    union 
   select null from dual )

or an or clause

   WHERE `e`.`lead_id` IN 
  (select lds.lead_id from mortgage_lead_leads lds 
    where lds.loan_officer_id=60 ) or `e`.`lead_id` is null

score 0 · Answer 2 · answered Mar 10 '16 at 20:17

you don't need to add it to the IN list:

WHERE (`e`.`lead_id` IN (select lds.lead_id from mortgage_lead_leads lds
                         where lds.loan_officer_id=60)
       OR `e`.`lead_id` is null)

please pay attention at brackets - it might be important if you are going to add another "AND condition" statements...

How to add an extra value to IN clause in mysql

2 Answers2