Pro SQL Queries

ArcGIS Pro SQL Queries (& Wildcards)

Clauses

Most queries and data filters in ArcGIS Pro can be performed using 'Clauses' from the various Query / Tool dialog boxes. This includes Select by Attribute, Definition Query, Label Class Queries, etc.

The basic 'Clause' works by:

  1. Adding a Clause
  2. Selecting an Attribute Field
  3. Selecting a Query Operator
  4. Selecting (or entering) Values or Fields

For example, to select Cities with a population over 100,000:

  • the FIELD would be Population
  • the QUERY OPERATOR would be Is Greater Than
  • and VALUE would be 100,000

For Select by Attribute, this query clause would look like:

SQL query screenshot

This would produce the query clause "Population Is Greater Than 100000" (note that you cannot use commas in the Query clause: 100000 not 100,000)

This Query Clause produces an SQL statement of: Population > 100000. (or more properly, Select Records from Cities Where Population > 100000). This is referred to as a 'Where Clause' because you are selecting features where a certain condition (the clause) is true. You can view the SQL statement by clicking the SQL button below the word 'Expression' in the query dialog.

SQL query screenshot

Multiple clauses can be combined by using the Add Clause button again. If you have more than one clause, they are combined using a Boolean Operator (either AND or OR). For example, you could select just the cities that are over 100,000 and that are located in Washington State by combining the query above with:

  • Population Is Greater Than 100000
  • AND State Is Equal To Washington

the SQL statement for this would be: Population > 100000 AND State = Washington

Alternatively, you could select all of the cities over 100,000 in addition to any city that is a state capital by combining the query above with:

  • Population Is Greater Than 100000
  • OR Capital Is Equal To Yes

the SQL statement for this would be: Population > 100000 OR Capital = Yes

Note the different Boolean Operators (AND vs. OR). The AND operator returns only those features that meet Both of the clauses. The OR operator returns any feature that meets either of the clauses. Typically there is also a NOT Boolean operator, but in ArcGIS Pro this is accomplished via the Invert Where Clause option / button (see below).

Fields and Query Operators

Any field can be used as the query Field.

The choices of Query Operator for a NUMERICAL field include:

  • Is Equal To
  • Does Not Equal
  • Is Greater Than
  • Is Greater Than or Equal To
  • Is Less Than
  • Is Less Than or Equal To
  • Includes the Value(s)
  • Does Not Include the Value(s)
  • Is Null
  • Is Not Null
  • Is Above Average
  • Is Below Average

The choices of Query Operator for a TEXT field include:

  • Is Equal To
  • Does Not Equal
  • Begins With
  • Ends With
  • Includes the Value(s)
  • Does Not Include the Value(s)
  • Contains the Text
  • Is Null
  • Is Not Null

The Includes the Value(s) option allows the selection of multiple Values (from a drop-down list of all possible values for that field). This would be the same as creating a series of Clauses using the OR Boolean Operator. So these two Queries would produce the exact same results:

  • Name Is Equal To Seattle OR City Is Equal To Bellingham OR City Is Equal To Everett
  • Name Includes the Value(s) Seattle, Bellingham, Everett

       the SQL statement for this would be: Name IN (Seattle, Bellingham, Everett) 

"Wildcards"

Most database systems allow the ability to use 'wildcards' with SQL queries (for text fields). Essentially, a wildcard is a symbol that says Select any field that includes a string of text, regardless of what other text might be before or after the chosen string. In ArcGIS Pro the % can be used in an SQL statement as a wildcard (there are not wildcards in query clauses). For example, here are a few SQL statements using wildcards:

  • Name LIKE %wood                 
    • would select: Birchwood, Cedarwood, Northwood, etc. (but not River Wood, since SQL is case sensitive)
  • Name LIKE East%                   
    • would select: East Valley, East Mapleton, East River Junction, etc. (but not Jonseville East, since the wildcard came after East, not before)
  • Name LIKE %Lake%               
    • would select: Moses Lake, Lake Stevens, Cottage Lake East, etc. (but not Westlake, since SQL is case sensitive)

Note the use of the LIKE query operator instead of Is Equal to for wildcard queries.

For the most part, in ArcGIS Pro you can create query Clauses without having to manually enter SQL statements with wildcards by using the various different Operators (and the Invert Where Clause option):

  • Name Ends with wood             
    • is the same as Name LIKE  %wood
  • Name Begins with East           
    • is the same as Name LIKE  East%
  • Name Contains the Text Lake   
    • is the same as Name LIKE  %Lake%

To get everything other than one of these wildcard operators you can use the Invert Where Clause button. Thus, to select Cities that don't have the word Lake in their name you can use:

  • Name Contains the Text Lake               INVERT WHERE CLAUSE

The Invert Where Clause option serves the same function as a NOT Boolean Operator. Thus these three queries would produce the same results (the first is a query clause and the second two are manually entered SQL statements): 

  • Name Contains the Text Lake               INVERT WHERE CLAUSE
  •  
  • Name LIKE  %Lake%                          INVERT WHERE CLAUSE
  • Name NOT LIKE  %Lake%

You can use the Invert Where Clause option with any query clause (or SQL statement). For example, to select all cities that have 100,000 or fewer people, you could use either of the following clauses:

  • Population Is Less Than or Equal To 100000
  • Population Is Greater Than 100000           INVERT WHERE CLAUSE