Filter features with definition queries

When you add a dataset as a layer to a map or scene, sometimes you only want to work with and draw a subset of the features in the dataset. Examples include the following:

• From a dataset of world cities, working with only those with a population greater than 1 million
• From a dataset of hydrology lines, working with only perennial streams
• From a dataset of parcels, working with only those that are zoned commercial

Definition queries allow you to define a subset of features to work with in a layer by filtering which features are retrieved from the dataset by the layer. This means that a definition query affects not only drawing, but also which features appear in the layer's attribute table and can be selected, labeled, identified, and processed by geoprocessing tools.

If you want to limit which features are drawn but retain all features from querying and processing, consider using display filters instead. Definition queries and display filters can be used together on a layer, but definition queries limit the features available in the layer, while display filters only limit which features are displayed.

Definition queries are composed of one or more clauses. They are written in SQL syntax, constructed using the query builder. See Introduction to query expressions to learn how to work with clauses and queries and how to save and load queries from files.

A layer can have multiple definition queries associated with it, but only one definition query can be active at a time. Even with multiple definition queries defined for a layer, all can be inactive so that no definition query is applied.

Note:

This topic outlines definition queries on a feature layer, but definition queries can be used in the same way on stand-alone tables.

Create a definition query for a layer

To create a definition query on a layer, follow these steps:

1. Ensure a layer is selected in the Contents pane.
2. On the Feature Layer tab set, click the Data tab.
3. On the Data tab, in the Definition Query group, click the Build Definition Query button to open the Layer Properties dialog box with its Definition Query tab open.
4. Click the New definition query button, or click its drop-down menu to create a new query in clause mode, SQL mode, or a query expression file (.exp).
5. In the query builder, choose values from the menus to construct the clause. These menus and their values are specific to the underlying source data.
6. Optionally, click Add Clause to add another clause to the query. Choose values from the menus in the new clause as required. Repeat this step to add additional clauses as necessary.
7. Optionally, click Field settings to modify the display of the field values. These settings are applied to all clauses.
   • Show field aliases is selected by default. Click Show field names to include the name of the table in the clause's field drop-down menu. This is especially helpful if your layer participates in a join that shares common field names.
   • Click Sort Ascending or Sort Descending to display the names of the fields in the drop-down menu in alphabetical order. Click Original order to return the fields to their default order.
   • Show visible fields is selected by default. Click Show all fields to display hidden fields in the clause's field drop-down menu.
   • Show domain and subtype values only is selected by default. Click Show all values to include any invalid or incorrect values in the field's drop-down menu of unique values.
8. Optionally, hover over the query name and click to rename it.
9. Optionally, click Verify the SQL expression is valid to verify the query.
10. To verify and finalize the query, in the query builder, click Apply.
11. If this is the only definition query on the layer, it becomes the active definition query, and is immediately applied. If there are other definition queries on the layer, click Set as Active to apply a definition query to the layer.

Set the active definition query

You can define more than one definition query for a layer or table, and switch between them. Only one is active at any time, or you can have no queries active. The definition queries are stored as a property of the layer.

To switch between active definition queries, follow these steps:

1. Ensure that a layer is selected in the Contents pane.
2. On the Feature Layer tab set, click the Data tab.
3. On the Data tab, in the Definition Query group, choose a query from the Definition Query menu to apply to the layer.
4. Alternatively, you can set the active query from the Definition Query tab of the Layer Properties dialog box.
   • Click Set as Active and click Apply to apply that query to the layer. Any other queries become inactive. The active query is named at the bottom of the Properties dialog box. If you have many queries listed and the active query is out of view, click Show the active definition query to scroll to it.
   • Click Clear active query and click Apply to have no query applied to the layer or table. All queries are retained by the layer or table, but none of them are currently applied.

Tip:

You can filter the list of layers in the Contents pane to show only those layers with an active definition query.

Modify existing queries

With the layer selected in the Contents pane, in the Feature Layer tab set, on the Data tab, in the Definition Query group, click the Build Definition Query button to open the Layer Properties dialog box with its Definition Query tab open. To modify a definition query, hover over the query and follow any of these actions:

• To modify, add, or remove clauses from the definition query, click Edit in the query's cell.
  • To edit a clause within a query, adjust values as necessary.
  • To remove a clause from the query, click Remove Clause .
  • To add a clause to the query, click Add Clause .

  Click Apply to apply the changes to the definition query.

• To remove the definition query from the layer or table, when not in editing mode, hover over the definition query cell and click Remove defintion query .
• To rename a definition query, click the name of a definition query and type a new name.
• To export the query syntax to a query expression file (.exp), when not in editing mode, hover over the definition query cell and click Save definition query .
• To replace the current query with a different query from a query expression file (.exp), when not in editing mode, hover over the definition query cell and click Load definition query .

Set definition queries on multiple layers at once

If multiple layers share definition queries with a common name, even if the content of the queries is not the same, the queries can be activated for the layers simultaneously. This means you can construct a number of related queries for a certain scenario and apply them all at once. You can then switch to a different set of queries to satisfy a different scenario, if necessary.

To apply definition queries with a common name to multiple layers simultaneously, follow these steps:

1. Ensure the layers are selected in the Contents pane.
2. On the Feature Layer tab set, click the Data tab.
3. On the Data tab, in the Definition Query group, choose a query from the Definition Query menu to apply to the layer.

   Only definition queries with a name common to all selected layers are shown.

Automate definition query creation

You can automate the creation and execution of definition queries with Python by using a combination of ArcPy functions. For information on this topic, including code samples, see the Layer class documentation.

Sharing layers with definition queries

When a layer is shared as a feature layer or within a map image layer, only the active definition query is retained and honored. All other inactive definition queries are dropped.