Building a query expression

Query expressions are used in ArcGIS to select a subset of features and table records. Query expressions in ArcGIS adhere to standard SQL expressions. For example, you use this syntax using the Select By Attributes tool or with the Query Builder dialog box to set a layer definition query.

This topic describes how to build basic WHERE clause expressions and is useful if you are just getting started with SQL. For a more detailed reference, see SQL reference for query expressions used in ArcGIS.

A simple SQL expression

SELECT * FROM forms the first part of the SQL expression and is automatically supplied for you.

Query expressions use the general form that follows a Select * From <Layer or dataset> Where clause (for example, the part of the SQL expression that comes after SELECT * FROM <Layer_name> WHERE).

Here is the general form for ArcGIS query expressions:

<Field_name> <Operator> <Value or String>

For compound queries, the following form is used:

<Field_name> <Operator> <Value or String> <Connector> <Field_name> <Operator> <Value or String> ...

Optionally, parentheses () can be used for defining the order of operations in compound queries.

Because you are selecting columns as a whole, you cannot restrict the SELECT to return only some of the columns in the corresponding table because the SELECT * syntax is hard-coded. For this reason, keywords, such as DISTINCT, ORDER BY, and GROUP BY, cannot be used in an SQL query in ArcGIS except when using subqueries. See SQL reference for query expressions used in ArcGIS for information on subqueries.

In most ArcGIS dialog boxes where you build a query expression, the name of the layer or table is supplied for you (or you select it from a drop-down list). For example:

The next part of the expression is the WHERE clause, which is the part you must build. A basic SQL WHERE clause would look like

STATE_NAME = 'Alabama'

This would select the features containing "Alabama" in a field named STATE_NAME.

SQL syntax

The SQL syntax you use differs depending on the data source. Each DBMS has its own SQL dialect.

To query file-based data, including file geodatabases, coverages, shapefiles, INFO tables, dBASE tables, and CAD and VPF data, you use the ArcGIS SQL dialect that supports a subset of SQL capabilities. To query personal geodatabases, you use the Microsoft Access syntax. To query an ArcSDE geodatabase, you use the SQL syntax of the underlying DBMS (that is, Oracle, SQL Server, DB2, Informix, or PostgreSQL).

ArcGIS dialog boxes in which you create SQL WHERE clauses will help you use the correct syntax for the database you're querying. They list the proper field names and values with the appropriate delimiters. They also select the relevant SQL keywords and operators for you.

Searching strings

Strings must always be enclosed within single quotes. For example:

"STATE_NAME" = 'California'

Strings in expressions are case sensitive except when you're querying personal geodatabase feature classes and tables. To make a case-insensitive search in other data formats, you can use a SQL function to convert all values to the same case. For file-based data sources like file geodatabases or shapefiles, use either the UPPER or LOWER function.

For example, the following expression will select customers whose last name is stored as either Jones or JONES:

UPPER("LAST_NAME") = 'JONES'

Other data sources have similar functions. Personal geodatabases, for example, have functions named UCASE and LCASE that perform the same operation.

Use the LIKE operator (instead of the = operator) to build a partial string search. For example, this expression would select Mississippi and Missouri among U.S. state names:

"STATE_NAME" LIKE 'Miss%'

% means that anything is acceptable in its place: one character, a hundred characters, or no character. Alternatively, if you want to search with a wildcard that represents one character, use _.

For example, this expression would find Catherine Smith and Katherine Smith:

"OWNER_NAME" LIKE '_atherine smith'

The wildcards above work for any file-based data or ArcSDE geodatabase. The wildcards you use to query personal geodatabases are * for any number of characters and ? for one character.

Wildcard characters appear as buttons on the Select by Attributes and Query Builder dialog boxes. You can click the button to enter the wildcard into the expression you're building. Only the wildcard characters that are appropriate to the data source of the layer or table you are querying are displayed.

If you use a wildcard character in a string with the = operator, the character is treated as part of the string, not as a wildcard.

You can use greater than (>), less than (<), greater than or equal (>=), less than or equal (<=), and BETWEEN operators to select string values based on sorting order. For example, this expression will select all the cities in a coverage with names starting with the letters M through Z:

"CITY_NAME" >= 'M'

The not equal (<>) operator can also be used when querying strings.

If the string contains a single quote you will first need to use another single quote as an escape character. For example:

"NAME" = 'Alfie''s Trough'

Learn more about wildcards

The NULL keyword

You can use the NULL keyword to select features and records that have null values for the specified field. The NULL keyword is always preceded by IS or IS NOT.

For example, to find cities whose 1996 population has not been entered, you can use

"POPULATION96" IS NULL

Alternatively, to find cities whose 1996 population has been entered, you can use

"POPULATION96" IS NOT NULL

Searching numbers

You can query numbers using the equal (=), not equal (<>), greater than (>), less than (<), greater than or equal (>=), less than or equal (<=), and BETWEEN operators.

For example

"POPULATION96" >= 5000

Numeric values are always listed using the point as the decimal delimiter regardless of your regional settings. The comma cannot be used as a decimal or thousands delimiter in an expression.

Calculations

Calculations can be included in expressions using the arithmetic operators +, -, *, and /.

Calculations can be between fields and numbers.

For example:

"AREA" >= "PERIMETER" * 100

Calculations can also be performed between fields.

For example, to find the countries with a population density of less than or equal to 25 people per square mile, you could use this expression:

"POP1990" / "AREA" <= 25

Operator precedence

Expressions are evaluated according to standard operator precedence rules. For example, the part of an expression enclosed in parentheses is evaluated before the part that isn't enclosed.

The example

"HOUSEHOLDS" > "MALES" * "POP90_SQMI" + "AREA"

is evaluated differently from

"HOUSEHOLDS" > "MALES" * ("POP90_SQMI" + "AREA")

You can either click to add parentheses and type the expression you want to enclose or highlight the existing expression that you want to enclose, then click the Parentheses button to enclose it.

Combining expressions

Complex expressions can be built by combining expressions with the AND and OR operators.

For example, the following expression would select all the houses that have more than 1,500 square feet and a garage for three or more cars:

"AREA" > 1500 AND "GARAGE" > 3

When you use the OR operator, at least one side of the expression of the two separated by the OR operator must be true for the record to be selected.

For example:

"RAINFALL" < 20 OR "SLOPE" > 35

Use the NOT operator at the beginning of an expression to find features or records that don't match the specified expression.

For example:

NOT "STATE_NAME" = 'Colorado'

NOT expressions can be combined with AND and OR.

For example, this expression would select all the New England states except Maine:

"SUB_REGION" = 'New England' AND NOT "STATE_NAME" = 'Maine'

Subqueries

A subquery is a query nested within another query and is supported by geodatabase data sources only. It can be used to apply predicate or aggregate functions or to compare data with values stored in another table. For example, this query would select only the countries that are not also listed in the table indep_countries:

"COUNTRY_NAME" NOT IN (SELECT "COUNTRY_NAME" FROM indep_countries)

For more information, see SQL reference for query expressions used in ArcGIS.

Querying dates

ArcGIS dialog boxes in which you create SQL WHERE clauses will help you use the correct data syntax for the database you're querying. Most of the time, you will only need to click the field, the operator, and the value to generate the proper syntax.