The answer? In SharePoint when you use the word "Query".
I recently used a tool entitled "U2U CAML Query Builder and Execution tool". Don't take this as a recommendation of this tool, its rough round the edges but it does what it says on the tin.
You can use this tool to connect to your SharePoint site, drill in to your SPList and, using a pre-populated choice of fields in your list, construct a CAML query.
You can test your queries from here too.
I was using it to generate the query I needed to add to my code:
private static SPListItemCollection GetIncompleteTasks(SPList taskList)
{
SPQuery query = new SPQuery();
query.Query = @"<Query><Where><IsNull><FieldRef Name='CompletionDate' /></IsNull></Where></Query>";
return taskList.GetItems(query);
}
However, there is a well hidden deceitful gotcha here. If you use the Query tag in your SPQuery.Query string it totally negates the rest of your query!. In this case it ignores the where clause and will always return the entire contents of the list.
This seems very misleading to me. If the CAML is malformed I would have anticipated an exception to be thrown so developers could know to remove the <Query> tag. The other alternative is to detect and ignore this tag, or worse case don't return any results. But to pretend to work and return all records!?
Resolution, remove <Query>:
private static SPListItemCollection GetIncompleteTasks(SPList taskList)
{
SPQuery query = new SPQuery();
query.Query = @"
<Where><IsNull><FieldRef Name='CompletionDate' /></IsNull></Where>";
return taskList.GetItems(query);
}