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, 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.
As shown in the image below, the interactive query builder contains various components:
- Buttons to group or ungroup clauses. The green check mark can be clicked to verify the syntax.
- A SQL toggle button switches the builder to the SQL mode.
- Preformatted drop-downs to build each query interactively by choosing fields, operators, and values.
- Add and build additional clauses, and remove them individually if they are no longer needed.
The query builder's interactive 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, 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 SQL mode. The benefits of working in SQL 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.
- Color-coded elements to help you visually verify or modify your query.
The SQL mode of the query builder contains the following components:
- A SQL toggle button switches to the interactive mode.
- The code editor colors keywords and strings to help you find text in your SQL query. The WHERE clause is assumed.
- The green check mark can be clicked to verify the 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.