CAML "NOT IN" query

Question

Is there a way to do something like "NOT IN" behavior in SharePoint 2010? I can easily implement IN behavior like that:

<Where>
   <In>
      <FieldRef Name="ID"/>
      <Values>
         <Value Type="Counter">1</Value>
         <Value Type="Counter">2</Value>
         <Value Type="Counter">3</Value>
         <Value Type="Counter">4</Value>
         <Value Type="Counter">5</Value>
      </Values>
   </In>
</Where>

But is there a way to select all the values that DOES NOT IN Values enumeration?

Here is the USE CASE: I have a Lookup field with AllowMultipleValues = true, and I need to get all items from LookupList, which are not included into Lookup field

Thanks in advance!

Answer 1

Starting from SharePoint 2010, there's the NotIncludes element that might work for you. From MSDN:

If the specified field is a Lookup field that allows multiple values, specifies that the Value element is excluded from the list item for the field that is specified by the FieldRef element.

Template:

<NotIncludes>
    <FieldRef Name="Field_Name" />
    <Value Type="Field_Type" />
    <XML />
</NotIncludes>

• More reading: NotIncludes Element

Answer 2

To get the opposite behavior of 'In', you have to make a nested 'Neq' query. 'NotIncludes' could be substituted for or combined with 'Neq' if you are dealing with a Lookup Field with multiple values.

<Query>
    <Where>
        <And>
            <And>
                <Neq>
                    <FieldRef Name="ID" /><Value Type="Counter">5</Value>
                </Neq>
                <Neq>
                    <FieldRef Name="ID" /><Value Type="Counter">13</Value>
                </Neq>
            </And>
            <And>
                <NotIncludes>
                    <FieldRef Name="children" /><Value Type="Lookup">20</Value>
                </NotIncludes>
                <NotIncludes>
                    <FieldRef Name="children" /><Value Type="Lookup">32</Value>
                </NotIncludes>
            </And>
        </And>
    </Where>
</Query>

If you want more variables then more nesting needs to be done. Have fun.