# ArcGIS Pro
| Category | Purpose | Example Query |
| --------------------- | ------------------------------ | ----------------------------------------------------------------------- |
| Basic Selection | Select all records | `1=1` |
| Basic Selection | Select by exact string match | `STATE_NAME = 'California'` |
| Basic Selection | Select by numeric value | `POPULATION > 500000` |
| Basic Selection | Select by range | `AREA_SQMI >= 100 AND AREA_SQMI <= 500` |
| Basic Selection | Exclude a value | `STATUS <> 'Inactive'` |
| Null Handling | Find null values | `FIELD_NAME IS NULL` |
| Null Handling | Find non-null values | `FIELD_NAME IS NOT NULL` |
| Null Handling | Null or empty string | `FIELD_NAME IS NULL OR FIELD_NAME = ''` |
| String Matching | Starts with | `NAME LIKE 'San%'` |
| String Matching | Ends with | `NAME LIKE '%ville'` |
| String Matching | Contains substring | `NAME LIKE '%creek%'` |
| String Matching | Single character wildcard | `CODE LIKE 'A_C'` |
| String Matching | Case-insensitive (File GDB) | `UPPER(NAME) = 'RIVERSIDE'` |
| Multiple Values | Match list of values | `COUNTY IN ('King', 'Pierce', 'Snohomish')` |
| Multiple Values | Exclude list of values | `TYPE NOT IN ('Residential', 'Commercial')` |
| Multiple Values | Multiple OR conditions | `CLASS = 'A' OR CLASS = 'B' OR CLASS = 'C'` |
| Numeric & Math | Compare calculated value | `POPULATION / AREA > 1000` |
| Numeric & Math | Round a field | `ROUND(REVENUE, 2) > 5000` |
| Numeric & Math | Absolute value | `ABS(ELEVATION) < 50` |
| Numeric & Math | Modulo (odd ObjectIDs) | `OBJECTID % 2 = 1` |
| Date & Time | Select by exact date (FGDB) | `SURVEY_DATE = DATE '2024-01-15'` |
| Date & Time | Select by date range | `SURVEY_DATE >= DATE '2023-01-01' AND SURVEY_DATE <= DATE '2023-12-31'` |
| Date & Time | Records from last N days | `SURVEY_DATE >= CURRENT_TIMESTAMP - 30` |
| Date & Time | Extract year from date | `EXTRACT(YEAR FROM SURVEY_DATE) = 2023` |
| Date & Time | Null date check | `INSPECTION_DATE IS NULL` |
| Compound Logic | AND with OR (use parentheses) | `STATUS = 'Active' AND (TYPE = 'Park' OR TYPE = 'Reserve')` |
| Compound Logic | NOT with LIKE | `NAME NOT LIKE '%Unnamed%'` |
| Compound Logic | Complex multi-condition | `SCORE >= 80 AND STATUS = 'Open' AND REGION IN ('North', 'East')` |
| Field Comparison | Compare two fields | `START_DATE < END_DATE` |
| Field Comparison | Fields that are equal | `CITY = STATE` |
| Field Comparison | Field value equals ObjectID | `REF_ID = OBJECTID` |
| String Functions | Trim whitespace | `TRIM(NAME) = 'Portland'` |
| String Functions | Substring match | `SUBSTRING(ZIPCODE, 1, 3) = '980'` |
| String Functions | String length filter | `CHAR_LENGTH(DESCRIPTION) > 100` |
| String Functions | Concatenate fields | `CITY \|\| ', ' \|\| STATE = 'Seattle, WA'` |
| Arcade Expressions | Use Arcade in definition query | `$feature.POPULATION / $feature.AREA_SQMI > 500` |
| Arcade Expressions | Text function in Arcade | `Upper($feature.NAME) == 'DENVER'` |
| Arcade Expressions | Date comparison in Arcade | `Year($feature.SURVEY_DATE) == 2024` |
| Hosted Feature Layers | Standard SQL for AGOL layers | `upper(state_name) = 'TEXAS'` |
| Hosted Feature Layers | Date filter on AGOL layer | `survey_date BETWEEN '2023-01-01' AND '2023-12-31'` |
| Hosted Feature Layers | AGOL LIKE query | `name LIKE '%Lake%'` |
# QGIS
| Category | Purpose | Example Query |
| ---------------- | ----------------------------- | ----------------------------------------------------------------------- |
| Basic Selection | Select all features | `1=1` |
| Basic Selection | Select by exact string match | `"STATE_NAME" = 'California'` |
| Basic Selection | Select by numeric value | `"POPULATION" > 500000` |
| Basic Selection | Select by range | `"AREA_SQMI" >= 100 AND "AREA_SQMI" <= 500` |
| Basic Selection | Exclude a value | `"STATUS" <> 'Inactive'` |
| Null Handling | Find null values | `"FIELD_NAME" IS NULL` |
| Null Handling | Find non-null values | `"FIELD_NAME" IS NOT NULL` |
| Null Handling | Null or empty string | `"FIELD_NAME" IS NULL OR "FIELD_NAME" = ''` |
| String Matching | Starts with | `"NAME" LIKE 'San%'` |
| String Matching | Ends with | `"NAME" LIKE '%ville'` |
| String Matching | Contains substring | `"NAME" LIKE '%creek%'` |
| String Matching | Case-insensitive match | `lower("NAME") = 'riverside'` |
| String Matching | Regular expression match | `regexp_match("NAME", '^San')` |
| String Matching | Not matching pattern | `"NAME" NOT LIKE '%unnamed%'` |
| Multiple Values | Match list of values | `"COUNTY" IN ('King', 'Pierce', 'Snohomish')` |
| Multiple Values | Exclude list of values | `"TYPE" NOT IN ('Residential', 'Commercial')` |
| Multiple Values | Multiple OR conditions | `"CLASS" = 'A' OR "CLASS" = 'B' OR "CLASS" = 'C'` |
| Numeric & Math | Compare calculated value | `"POPULATION" / "AREA" > 1000` |
| Numeric & Math | Round a field | `round("REVENUE", 2) > 5000` |
| Numeric & Math | Absolute value | `abs("ELEVATION") < 50` |
| Numeric & Math | Modulo (odd feature IDs) | `$id % 2 = 1` |
| Numeric & Math | Between range | `"SCORE" BETWEEN 50 AND 100` |
| Date & Time | Select by exact date | `"SURVEY_DATE" = '2024-01-15'` |
| Date & Time | Select by date range | `"SURVEY_DATE" >= '2023-01-01' AND "SURVEY_DATE" <= '2023-12-31'` |
| Date & Time | Extract year from date | `year("SURVEY_DATE") = 2023` |
| Date & Time | Extract month from date | `month("SURVEY_DATE") = 6` |
| Date & Time | Records from last N days | `"SURVEY_DATE" >= now() - interval(30, 'days')` |
| Date & Time | Null date check | `"INSPECTION_DATE" IS NULL` |
| Compound Logic | AND with OR (use parentheses) | `"STATUS" = 'Active' AND ("TYPE" = 'Park' OR "TYPE" = 'Reserve')` |
| Compound Logic | NOT with LIKE | `"NAME" NOT LIKE '%Unnamed%'` |
| Compound Logic | Complex multi-condition | `"SCORE" >= 80 AND "STATUS" = 'Open' AND "REGION" IN ('North', 'East')` |
| Field Comparison | Compare two fields | `"START_DATE" < "END_DATE"` |
| Field Comparison | Fields that are equal | `"CITY" = "STATE"` |
| Geometry | Filter by area (sq metres) | `$area > 10000` |
| Geometry | Filter by length | `$length > 500` |
| Geometry | Filter by feature ID | `$id = 42` |
| Geometry | Filter by x coordinate | `x($geometry) > -120` |
| Geometry | Filter by geometry type | `geometry_type($geometry) = 'Polygon'` |
| String Functions | Trim whitespace | `trim("NAME") = 'Portland'` |
| String Functions | Substring match | `left("ZIPCODE", 3) = '980'` |
| String Functions | String length filter | `length("DESCRIPTION") > 100` |
| String Functions | Concatenate fields | `"CITY" \|\| ', ' \|\| "STATE" = 'Seattle, WA'` |
| String Functions | Convert to upper/lower | `upper("NAME") = 'DENVER'` |
| QGIS Expressions | Use a virtual field value | `"POPULATION" / $area * 1000000` |
| QGIS Expressions | Check geometry validity | `is_valid($geometry)` |
| QGIS Expressions | Features within distance | `within($geometry, buffer(@map_extent_center, 5000))` |
| QGIS Expressions | Filter by map variable | `"REGION" = @atlas_pagename` |
| QGIS Expressions | Aggregate (related table) | `relation_aggregate('rel_id', 'sum', "COST") > 1000` |