What's the difference between a "nested/sub query" and an "additional query"?

Introduction

When you want to run a complex search in Little Green Light that you can't create by simply adding more criteria, you can build a complex search using an additional query, nested/subquery, or combination of additional queries and nested/subqueries. Here are a few examples where this applies:

Finding constituents, gifts, or other record types that have different sets of criteria in common
Excluding records from a search
Controlling the order of operations in a search

Using an additional query or a nested/subquery gives you control over the order of operations within your search in different ways. An additional query applies to the entirety of the search, including all the queries and subqueries within it. A nested/subquery allows you to refine the data (using the “or all” joining statement) within the individual search statement it is applied to.

In general, use a nested query only when you want to use an “or all” joining statement. In all other cases, use an additional query.

What is an additional query in a search?

An additional query allows you to add another search with its own set of criteria to an existing search. To add an additional query, use the "Advanced options" link in your search. This opens a dropdown menu where you can select Add additional query.

Once you select for the additional query to be added, you’ll see a second search that you can add additional criteria to using the Add more criteria option.

It is important to note that a joining statement connects the two queries together. By default, the joining statement is “and all”; other available options are “or all” and “and not”. The joining statement dictates how the additional search will work as part of the search as a whole.

In the example above, the search will return constituents who have made a gift that meets both sets of criteria:

They gave any kind of gift this year

and also

They gave to the Capital Campaign last year

Anytime the joining statement is changed, the outcome of the search is changed. If the joining statement were set to “and not”, the search would return constituents who have given a gift this year, and it would exclude anyone who had also made a Capital Campaign gift last year.

If the search needs to return constituents who gave gifts this year or gave a gift last year to the Capital Campaign, we recommend using a nested/subquery rather than an additional query.

NOTE: The guideline for “or all” queries is that you should always nest them.

What is a nested/subquery in a search?

A nested/subquery is just like an additional query, except for the order of operations. The parent query and all of its nested queries will be evaluated together as a unit. In general, you should always nest “or all” queries and never nest any other type of query. To add a nested/subquery, click the “Advanced options” link in your search to open the dropdown menu and then select Add nested/subquery. Like an additional query, nested/subqueries will be joined by default to the main search using the statement “and all”, but this can be updated to “or all” or “and not”.

An example of a nested/subquery is shown below. You will notice that unlike an additional query, the nested/subquery is set within the original query and runs simultaneously with the original query.

The example above will return all constituents who have volunteer dates this year or have given a gift this year.

A nested subquery is most often used for “or all” queries. It should always be used for “or all” queries when combined with additional queries, as explained further below.

Examples of where a nested/subquery should be used

If you are running a constituent search to find anyone who donated last year or is a current parent and has not yet donated this year, you can set up the search using a combination of additional queries and nested/subqueries, as shown below.

Another example is using a combination of additional queries and nested/subqueries to find constituents who gave a cumulative amount of $1,000 or more or a single gift of $100 or more to the annual appeal last fiscal year, but who have also not given this year.

NOTE: The guideline for “or all” queries is that you should always nest them, unless you want all queries at the top level of your search to use the “or all” joining statement. Nesting will ensure that any additional criteria you add to your search, such as exclusions (“and not” queries), will apply to your entire result set.

Using a constituent list in a complex search

Constituent lists can be used to pull together sets of constituent records when your search criteria are particularly complex. Lists can further be used in conjunction with additional queries and nested/subqueries as extra help for difficult-to-write searches.