9.1 Attribute Selections & SQL Queries

9.1 Attribute Selections & SQL Queries

Selecting features based on their tabular attribute values is a fundamental workflow in Geographic Information Systems (GIS). In ArcGIS Pro, attribute querying allows GIS analysts to isolate specific subsets of spatial data based on numeric thresholds, text strings, date ranges, or coded domains stored in feature layer attribute tables. Attribute selection is non-destructive; it highlights matching records in the attribute table and visually emphasizes corresponding geometries on the map canvas with a cyan highlight by default, leaving non-selected features intact and unedited.

The Select Layer By Attribute Geoprocessing Tool

The primary mechanism for attribute querying in ArcGIS Pro is the Select Layer By Attribute geoprocessing tool, accessible from the Map ribbon (Selection group), the Contents pane context menu, or the Geoprocessing pane.

Interface & Operational Modes: Clause Builder vs. SQL Mode

ArcGIS Pro provides two interface modes for defining attribute queries:

1. Clause Builder (Visual Mode): Designed for user-friendly query construction. Analysts choose a Field, an Operator (such as is equal to, is greater than, is null, or begins with), and a Value. ArcGIS Pro automatically formats the entry into standard SQL syntax appropriate for the underlying data source format (e.g., File Geodatabase, Enterprise Geodatabase, Mobile Geodatabase, or Shapefile).
2. SQL Mode (Direct Code Mode): Toggling to SQL mode reveals the raw SQL WHERE clause string. This mode is essential for entering advanced expressions, subqueries, complex functions, or mathematical operations on fields that exceed Clause Builder capabilities.

---

SQL Clause Structure & Core Operators

Standard attribute queries adhere to ANSI SQL standards, though syntax details (such as string quotes or case-sensitivity) vary by backend storage format (File Geodatabase vs. Enterprise SQL Server or PostgreSQL).

Basic Field Comparison Operators

• Equality (=): POPULATION_2020 = 50000 evaluates features where the numeric attribute exactly equals 50,000.
• Inequality (<> or !=): STATUS <> 'Inactive' selects all records except those marked Inactive.
• Relational Comparison (>, <, >=, <=): Used for numeric or date ranges, such as ACRES >= 100 or INSPECT_DATE < '2024-01-01'.

Compound Queries and Boolean Logic (AND, OR, NOT)

When combining multiple search conditions, Boolean logical operators govern how criteria are combined:

• AND Operator: Requires both conditions to be true for a feature to be selected. ZONING = 'Commercial' AND VALUE > 500000 restricts results to commercial parcels valued above $500,000. Using AND reduces the total count of selected features (set intersection).
• OR Operator: Requires either condition (or both) to be true. STATE_NAME = 'Oregon' OR STATE_NAME = 'Washington' selects features in either state. Using OR expands the total count of selected features (set union).
• NOT Operator: Inverts a condition. NOT (CLASS = 'Private') selects all features where the class is not Private.

Precedence Note: In complex SQL statements containing both AND and OR operators, AND takes higher precedence than OR. Use parentheses () to explicitly define evaluation order, such as (COUNTY = 'King' OR COUNTY = 'Pierce') AND USE_TYPE = 'Industrial'.

String Matching & Wildcard Operators (LIKE, %, _)

For text attributes, exact matching is often insufficient. The LIKE operator enables pattern matching using standard wildcard characters:

• Percent Sign (%): Represents zero, one, or multiple arbitrary characters. STREET_NAME LIKE 'Main%' matches 'Main', 'Main St', 'Main Street', and 'Main Boulevard'.
• Underscore (_): Represents exactly one single character. PARCEL_ID LIKE 'A_99' matches 'A199' and 'A299', but not 'AB99' or 'A1299'.

The IN Operator for Discrete Lists

Rather than stringing multiple OR statements together, the IN operator specifies a list of discrete explicit values: PARCEL_ZONE IN ('R-1', 'R-2', 'R-3') is functionally equivalent to PARCEL_ZONE = 'R-1' OR PARCEL_ZONE = 'R-2' OR PARCEL_ZONE = 'R-3', but yields much cleaner and faster-executing SQL syntax.

Checking for Missing Values (IS NULL and IS NOT NULL)

In database management, a NULL value signifies missing, unrecorded, or unknown data—distinct from a zero (0) or blank empty string (''). Standard comparison operators (= NULL or <> NULL) fail in SQL evaluation.

• IS NULL: Identifies records with no entry in the targeted field: INSPECTOR_NAME IS NULL.
• IS NOT NULL: Filters for records containing valid populated data: ELEVATION IS NOT NULL.

---

Selection Types (Combination Modes)

The Selection Type parameter in the Select Layer By Attribute tool controls how new query results interact with pre-existing map selections:

• New selection: Replaces any existing selection on the layer with the features matching the current query.
• Add to current selection: Evaluates the query across all features in the dataset and appends matching features to the existing selection set (union).
• Remove from current selection: Evaluates the query across currently selected features and deselects any features that meet the criteria.
• Select subset from current selection: Evaluates the query only within the existing selection set, narrowing down the selected features (intersection).
• Switch selection: Inverts the current selection set, selecting all unselected features and deselecting all currently selected features.

---

Managing Selections & Exporting Selected Features

Once a selection is created:

• Attribute Table Operations: Opening the layer's attribute table allows users to toggle between viewing all records and viewing only selected records using the Show Selected Records button.
• Clearing Selections: Click the Clear Selection button on the Map ribbon or Table window to remove active highlight states across all or specific layers.
• Exporting Selected Features: Selections in ArcGIS Pro act as temporary subsets for display and geoprocessing operations. To make a selection permanent as an independent dataset, right-click the layer in the Contents pane, navigate to Data > Export Features, and save the selected records to a new File Geodatabase feature class or shapefile. If a selection exists on the input layer during any geoprocessing operation (e.g., Buffer, Clip), ArcGIS Pro automatically processes only the selected features.