Queries are used to select a subset of features and table records. All query expressions in ArcGIS Pro use Structured Query Language (SQL) to formulate these search specifications.
You use the query builder to construct queries. The query builder is encountered in a few places. You use it when defining the definition query for layers and stand-alone tables, and also when selecting features by attributes.
- To set the definition query for a layer highlighted in the Contents pane, under Feature Layer, on the Data tab, in the Definition Query group, click Build Definition Query to open the Layer Properties dialog box to the Definition Query tab.
- To set the definition query for a stand-alone table highlighted in the Contents pane, under Standalone Table, on the Data tab, in the Definition Query group, click Build Definition Query to open the Standalone Table Properties dialog box to the Definition Query tab.
- To select features by their attributes, on the Map tab in the Selection group, click Select By Attributes to open the Select By Attributes geoprocessing tool. In the Expression parameter, construct a query to select attributes.
The query builder has two options for authoring a SQL query:
- Clause mode: build a query interactively. A query is comprised of one or more clauses. This is the default mode.
- SQL mode: type a query in SQL syntax. Use this mode to access all SQL capabilities.
You can begin building a clause in the Clause mode, then click SQL to see the clause in native SQL syntax. Using this as a starting point, you can refine the clause as necessary from there.
Be aware that if you add SQL syntax that is not supported by the Clause mode, you may not be able to switch back to that mode.
Use Clause mode to construct a query by making choices from drop-down menus that are populated and prefiltered based on the chosen field type.
The benefits of working in Clause mode include the following:
- You can construct valid SQL queries regardless of the data source.
- You can build common queries with no prior knowledge of SQL.
- The conditional operators are filtered based on the chosen field type.
- It is easy to apply grouping to improve the order of your operations.
Clause mode is well-suited for constructing queries against string, numeric, and date fields, but is limited to the types of queries you can create. To write a query without restriction, use the SQL mode instead.
Construct a query in Clause mode
Follow these steps to construct a query by building one or more clauses:
- In the query builder, click Add Clause .
- Choose values from the drop-down menus to construct the clause. These menus, and their values, are specific to the underlying source data.
- Click Update to verify and add the clause to the query.
- Optionally click Add Clause to add another clause to the query.
Alternatively, you can load an existing query. Click Load to load a query from a query expression file (.exp file extension). If it is a complex expression that cannot be evaluated in Clause mode, the clause opens in SQL mode instead.
If you are experienced writing SQL syntax, you may prefer to build and manage queries in SQL mode. The benefits of working in SQL mode include the following:
- You can write queries without restrictions.
- You can leverage autocomplete. As you type, a prompt appears, showing only keywords and operators supported by your data source.
- Color-coded elements to help you visually verify or modify your query.
In SQL mode, you must use proper SQL syntax when forming a query to ensure that the data source knows what records to return. You do not need to delimit fields with special characters.
Queries are either simple or compound.
Simple queries contain a single clause, for example, STATE_NAME = 'Alabama'. This query selects all features containing the text Alabama in the STATE_NAME field.
Compound queries are comprised of multiple clauses connected by a logical operator, AND or OR. You can also use parentheses in compound queries around clauses to define the order of operations.
For example, STATE_NAME = 'Alabama' OR (STATE_NAME = 'Wyoming' AND POP2000 > 10000) selects all features containing Alabama in the STATE_NAME field, and all the features that contain both Wyoming in the STATE_NAME field and have a value greater than 10,000 in the field named POP2000.
If you frequently work with databases, you may already have experience using SQL and be accustomed to two key aspects, the SELECT statement and the WHERE clause:
- The SELECT statement is used to select fields from a layer or table.
- The WHERE clause is used to get records that meet specific criteria.
You must provide the WHERE clause, and decide which criteria are important for your query.
When working with the query builder, you only provide the WHERE clause unless you include subqueries. Subqueries are queries nested within another query.
See the SQL reference guide for more information about elements used in queries.
The SQL syntax you use differs depending on the data source. Each database management system (DBMS) has its own SQL dialect. To query file-based data, including file geodatabases, shapefiles, dBASE tables, and CAD and VFP data, you use the ArcGIS SQL dialect that supports a subset of SQL capabilities. To query an enterprise geodatabase, you use the SQL syntax of the underlying DBMS (that is, Oracle, SQL Server, PostgreSQL, DB2, or Informix). The same applies when accessing a database directly (such as Netezza).
Modify an existing query
To modify a query, follow any of these actions:
- To edit a clause, hover over the clause and click Edit Clause to open the clause component menus. Adjust values as necessary.
- To remove a clause from the query, hover over the clause and click Remove Clause .
- To add a clause to the query, click Add Clause .
- To remove the query from the definition query of a layer or table entirely, click Remove Query . If you are accessing the query builder elsewhere, such as from the Select Layer By Attribute tool, click Clear to remove the entire query.
- To export the query syntax to a query expression file, click Save .
- To replace the current query with a different query from a query expression file (.exp file extension), click Load .