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