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 numerous places, including:
- Selecting features by their attributes in the Select Layer By Attribute geoprocessing tool
- Setting definition queries on layers or stand-alone tables
- Setting display filters on layers
- Creating reports
Construct queries
You can author queries natively by typing SQL syntax, or you can build a query interactively by selecting components. Writing SQL syntax gives you access to all SQL capabilities. To write a query in SQL syntax, click the SQL toggle button to switch to the SQL mode.
Construct a query interactively
In the default mode of the query builder, you can 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 this 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.
This 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. You can begin building a clause in the interactive mode, and then click the SQL toggle button 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 interactive mode, you may not be able to switch back to that mode afterward.
See Construct and modify queries to learn more about working in the interactive mode.
Construct a query by writing SQL syntax
If you are experienced in writing SQL syntax, you may prefer to build and manage queries in SQL mode. Click the SQL toggle button to switch to the 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.
SQL syntax
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. See the SQL reference guide for more information about writing SQL queries.
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 when you include subqueries. Subqueries are queries nested within another query.
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 access or query an enterprise geodatabase, you must use the SQL syntax of the underlying database management system (DBMS).