Introduction to query expressions

Queries are used to search for and select a subset of features and table records. All query expressions in ArcGIS AllSource use Structured Query Language (SQL) to formulate these search specifications.

The query builder is used to construct the queries in numerous places, including the following:

• 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

Query builder components

The query builder is an interface that helps you construct and modify queries in a structured format. With the query builder, you can author queries natively by typing SQL syntax or interactively by selecting components. Writing SQL syntax is the more advanced option but gives you access to all SQL capabilities.

Construct a query interactively

In the default mode of the query builder, SQL Designer, 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 designer 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.
• You can apply grouping to improve the order of your operations.

As shown in the image below, the interactive query builder contains various components:

1. Buttons to group or ungroup clauses. The green check mark can be clicked to verify the syntax.
2. A toggle button switches the builder to SQL Editor mode.
3. Preformatted drop-down menus to build each query interactively by choosing fields, operators, and values.
4. Add and build clauses, and remove them individually if they are no longer needed.

The query builder's designer 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 SQL Editor mode instead. You can begin building a clause in designer mode, and click the SQL Editor 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 in designer mode, you may not be able to switch back from editor mode.

See Construct and modify queries to learn more about working in designer 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 Editor mode. Click the SQL Editor toggle button to switch to editor mode. The benefits of working in editor mode include the following:

• You can write queries without restrictions.
• You can use autocomplete. As you type, a prompt appears, showing only keywords and operators supported by your data source.
  • Autocomplete can be turned off from the Settings menu in the Layer Properties window. This setting applies to all query builder controls in the application.
• Color-coded elements to help you visually verify or modify your query.

The editor mode of the query builder contains the following components:

1. When clicked, the activated SQL Editor toggle button switches back to designer mode.
2. The code editor colors keywords and strings to help you find text in your SQL query. The WHERE clause is assumed.
3. When clicked, the green check mark verifies the syntax.

In editor mode, you must use proper SQL syntax when forming a query to return the correct records from the data source. You do not need to delimit fields with special characters. See the SQL reference guide for more information about writing SQL queries.