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. For example, you will need to use SQL when you construct a query using the Select Layer by Attributes geoprocessing tool or when building a definition query for a layer or table.
Two ways you can write queries in the query builder
The Query Builder has two options for authoring your SQL query. You can switch between the two modes in the same dialog box (or pane, if using the Select By Attributes geoprocessing pane) depending on your preference. By default, the Query Builder opens in Clause mode which is intended to be approachable for any skill level and uses buttons to rely less on manual entry and knowledge of SQL syntax. The SQL Edit mode is for more experienced users who want to type their statement directly. It also means you will have fewer restrictions while building your statement. Click the SQL Edit button to build your query by typing the SQL syntax directly.
The Query Builder opens by default in Clause mode. Somewhat like a wizard, it allows you to use buttons and lists to construct your query. Your options are populated and prefiltered based on your chosen field type. The benefits of working in Clause mode include the following:
- You can construct valid SQL queries regardless of your 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 means it is easier for you to construct queries against string, numeric, and date fields. Clause mode is, however, limited to the types of queries you can create. To write a query without restriction, use SQL Edit mode.
To edit or remove an existing clause, you can hover over the clause and click Edit Clause or Remove Clause . After updating your clause you can apply the changes by clicking Update, or discard any changes by clicking Cancel.
SQL Edit mode
If you are experienced with using SQL, you might be more efficient using SQL Edit mode in the Query Builder. The main benefit over Clause mode is that SQL Edit mode offers the following:
- Query authoring without restrictions.
- Support for autocomplete so you can quickly build a query—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.
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.
When working with the Query Builder the SELECT statement is provided for you as SELECT * FROM layer or table. This means all fields will be used in the query sent to your data source. It is up to you to provide the WHERE clause and decide which criteria are important for your query. Keep in mind that because SQL Edit mode allows you to work with all of the SQL keywords and functions, you might not be able to switch back to Clause mode to continue building your query. You also do not need to delimit fields with special characters. ArcGIS Pro formats all query expressions before sending them to the data source.
When creating a query layer, you need to include the SELECT statement. This is the only case where you are required to input an SELECT statement in your SQL query.
In SQL Edit mode you need to use the proper SQL syntax when forming a query so your data source knows what records to return. You do not need to delimit fields with special characters. ArcGIS Pro formats all query expressions before sending them to the data source. Generally, a query is simple or compound. A simple query with the hard-coded SELECT statement and WHERE clause would look like this:
Simple query example: STATE_NAME = 'Alabama'
This would select the features containing "Alabama" in the file named STATE_NAME.
A compound query is a simple query with multiple clauses connected together by logical operators (AND, OR). You can also use parentheses in compound queries around clauses to define the order of operations.
Compound query example: STATE_NAME = 'Alabama' OR (STATE_NAME = 'Wyoming' AND POP2000 > 10000)
This would select the features containing "Alabama" in the field named STATE_NAME and the features that contain both "Wyoming" in the field named STATE_NAME and a value greater than 10,000 in the field named POP2000.
Since the SELECT statement is hard coded, you cannot restrict the query to select from a subset of fields. For this reason, keywords such as DISTINCT, ORDER BY, and GROUP BY can only be used when including subqueries—which are queries nested within another query. Use 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).
Keep in mind that because SQL Edit mode allows you to work with all of the SQL keywords and functions, you might not be able to switch back to Clause mode to continue building your query. You will receive the following warning if the SQL expression is advanced and you attempt to switch back to Clause mode:
The expression can't be edited in Clause mode.
Access to the definition query builder
There are a few ways you can gain access to the query builder if you need to perform a query on your feature layer or table records, including:
- The Layer Properties dialog box. Right-click on a layer in the Contents pane and click Properties to open the Layer Properties dialog box. From the Definition Query page click Add Clause to begin.
- Open the Definition Query expression builder directly from the ribbon. For a selected feature layer, click the Data tab and then click the dialog box launcher to open the Definition Query page on the Layer Properties dialog box to enter your query expression.
- Use the Select By Attributes geoprocessing tool pane. Provide a SQL expression to generate a subset of features for your layer or table. On the Map tab in the Selection group, click Select By Attributes to open the geoprocessing pane.
Verify an expression
You can verify the expression at any time in both Clause and SQL Edit mode by pressing Verify to check the expression is valid. The results you may see include a pass, a warning or an error.
- Pass: The SQL expression is valid. This means the syntax is valid and at least one feature will be returned with the query.
- Warning: The SQL expression is valid but no records were returned. This means the syntax is valid, but no rows or features will be returned with the query.
- Error: The SQL expression has invalid syntax. This result should only occur in SQL Edit mode as Clause mode prevents you from creating an expression with invalid syntax.
Clear an expression at any time using the Clear button . This clears all the clauses in Clause mode and clears the expression in SQL Edit mode.
Save and load expressions
Your expressions can be saved to a text file. When loading an expression if it is a simple expression that can be evaluated in Clause mode the Query Builder will default to Clause mode. Otherwise it will show the expression in SQL Edit mode. The buttons for Load or Save an expression are located on the lower corner of the expression builder window next to Verify and Clear .