Skip to main content
Filters a query using the results of another query. Returns the rows that are not included in the other query’s results. In SQL, this generates a WHERE clause that looks like WHERE NOT IN (SELECT... where the SELECT clause is the filtering query.
To include data using the results of another query, use field_name_in_query.

​
Requirements

Only supported for use in query views. Refer to the Examples section for the complete syntax.

​
Syntax

<field_name>:
  field_name_not_in_query: <filtering_field_name>

​
Properties

​
field_name
string
required
The field to filter on, specified in the format view_name.field_name. Can be a time, numeric, or string field.
​
field_name_not_in_query
string
required
The name of the field in the filtering query, specified in the format view_name.field_name. This field must contain the same data and be the same data type as the field_name.For example, if you filter on a customers.country field, filtering_field_name should contain a field with country data.

​
Examples

Click the SQL tab in a code block to view the query’s underlying SQL definition.
The first query in this example - shown below - lists the top 5 countries with the highest number of customer sign ups: 
| customers.country | customers.count |
|-------------------|-----------------|
| United States	    | 71              |
| Mexico            | 23              |
| Singapore         | 15              |
| Italy	            | 14              |
| Portugal          | 9               |
The second query applies a filter that uses the results of Query 1. Because the field_name_not_in_query excludes data, the results should not include United States, Mexico, Singapore, Italy, and Portugal: 
| customers.country | orders.count |
|-------------------|--------------|
| Netherlands       | 12           |
| Denmark           | 11           |
| Japan             | 11           |
| South Africa	    | 9            |
| Finland           | 6            |
The underlying YAML for a query view created from Query 2 would look like the following:
Query view filtered using the results of another query
label: Total orders - Excluding most popular countries

query:
  fields:
    customers.country: country
    orders.count: dim_count
  base_view: orders
  filters:
    customers.country:
      field_name_not_in_query: customers.country
      query_structure:
        fields: [ customers.country, customers.count ]
        base_view: customers
        limit: 5
        sorts:
          - field: customers.count
            desc: true
        offset: 0
  sorts:
    - field: customers.country
      desc: true

dimensions:
  dim_count: {}

  country:
    primary_key: true

measures:
  count:
    aggregate_type: count