Structured Query Language (SQL) is a standard programming language used to access, manipulate, and extract information. You might have heard SQL pronounced as “Ess-cue-ell” or you may have heard it referred to as “Sequel”. The latter originated from SQL being a descendent of an IBM language called SEQUEL (Structured English Query Language). When SQL was modified and adopted as a standard outside of IBM, the pronunciation stuck even though the name changed. Either pronunciation is correct, and both are commonly used.
In this article, you will learn what the difference between an expression and a dialect is, and what you can do with SQL in ArcGIS Pro.
What is a SQL Expression?
A SQL expression is formatted based on the syntax required to complete the query. The query expression has three major components:
- Operation
- Target
- Condition
OPERATION
A SQL operation is also referred to as a SQL command. This part of the expression is telling the system what action you want to take. Some of the most commonly used SQL operations are listed below.
- Create
- Insert
- Update
- Delete
- Select
- Drop
CONDITION
A SQL condition is also known as the SQL WHERE clause. This is the condition that is used to filter the records that will be acted upon. The most common conditions are listed below.
- Equals
- Greater than
- Less than
- Greater than or equal to
- Less than or equal to
- Not equal
- Between
- Like
Most of the time, the SQL condition will consist of a Field Name, Condition Operator, and a Value or String. In the example below, you can see two conditions are set. The first condition is requiring that STATUS is equal to ‘Not Supporting’, while the second condition is requiring that features are also greater than 5 acres. Because the operator ‘And’ is being used, both conditions must be true.
TARGET
The target defines which database, table, file, or layer you want the SQL operation to act on. Basically, the target is the data source of the query expression, and this is where things can get tricky. Each data source has its own variation of SQL. These variations are referred to as Dialects.
What SQL Dialects are used in ArcGIS Pro?
If you are working with file-based data, such as shapefiles, geodatabases, and feature services, then you will use the ArcGIS SQL dialect which supports a subset of SQL capabilities. Specifically, only the SELECT operation can be used for any ArcGIS SQL expression. This allows you to either query or select a subset of features or records.
To query Relational databases or enterprise geodatabases, you will need to use syntax associated with the underlying Relational Database Management System (RDBMS). In other words, if you use Oracle then you will use the Oracle dialect, and if you use SQL Server then you will use the SQL Server dialect. Each of these types of syntax are slightly different from one another.
What can I do with SQL in ArcGIS Pro?
SQL in ArcGIS Pro gives you more control over how you interact with your layers and data, and over how your data is visualized. The following are common examples of how SQL is used in ArcGIS Pro.
If you need to select a subset of features in a layer, you can use the Select by Attributes tool. This tool requires you to build or enter a SQL expression that defines the criteria for your selection. Once your subset is selected, you can then run a calculation on the features, export them as a new layer, delete them, and more.
If you only want to work with a subset of records that meet specific criteria and have that subset honored throughout your entire ArcGIS Pro Project, then you can set a definition query on the layer. Once you set a definition query, the only features displayed in the map and attribute table will be those that meet your criteria.
There may be instances where you only want to set a display filter, so that you can still access all records in the attribute table but only see features in the map that meet your criteria. You can set display filters from the Feature Layer menu at the top of the project, or directly from the symbology pane.
Why Should I use SQL?
When effectively applied, SQL will make your life easier and give you more control. Working with subsets of big data is one of the best ways to streamline your workflows, especially when updates or analyses only need to occur with records that meet specific criteria. I cannot express how many times I have used each of the examples above to quickly meet my deadlines.
We hope you have enjoyed this quick article on SQL expressions and dialects.
