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
- Writing definition queries for layers or stand-alone tables
- Creating reports
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.
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.
The 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. 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.
See Construct and modify queries to learn more about working in Clause mode.
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. 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 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).