Building SQL Query Expressions

Structured Query Language (SQL) is a database query and programming language used for accessing data and querying, updating, and managing relational database systems.

Below, using the Province_R dataset from the sample data PopulationAndEconomy workspace as an example, we introduce how to build common SQL Query expressions.

Numeric Queries

For numeric queries, common operators include '=', '<>', '>', '<', '<=', '>=', 'Between...and...', etc.

Example: Province_R.Pop_2014 Between 5000 and 10000

This query retrieves objects where the 2014 population (Pop_2014 field value) is between 50 million and 100 million.

Note:

When querying double-precision type values, it is not recommended to directly use '=' for comparison, as it may not return results due to precision issues.

String Queries

Using the keyword 'like', you can perform the following three types of string queries.

  • Partial match, using the '%' character.

    Example: Province_R.Name like 'Shan%' or Province_R.Name like 'Shan%'

    This query retrieves objects in the Province_R dataset where the Name field starts with 'Shan'.

  • Exact match. In database data sources, only single quotes can be used; in UDB/UDBX data sources, either single or double quotes can be used.

    Example: Province_R.Name like 'Beijing' or Province_R.Name like 'Beijing'

    This query retrieves objects in the Province_R dataset where the Name field value is 'Beijing'.

  • Single-character match, using the '_' character.

    Example: Province_R.Name like 'He__'

    This query retrieves objects in the Province_R dataset where the Name field starts with 'He' and is followed by exactly two characters.

Specific Value Queries

To query any value in a specified list, use the keyword 'in'.

  • Example 1: Province_R.Name in ('Beijing','Shanghai')

    This query retrieves objects in the Province_R dataset where the Name field value is 'Beijing' or 'Shanghai'.

  • Example 2: Province_R.ColorID in (1,4)

    This query retrieves objects in the Province_R dataset where the ColorID field value is 1 or 4.

Note:

For character-type fields, the query value needs to be enclosed in single or double quotes. However, in database data sources, only single quotes can be used.

Null Value Queries

To query records with null values, use the keyword 'is NULL'.

Example: Province_R.Name Is NULL

This query retrieves objects in the Province_R dataset where the Name field value is null.

Combined Statements

You can use the keywords 'and', 'or', 'not' to combine any valid conditional expressions.

Example: Province_R.GDP_2014 > 10000 and Province_R.IncomeLevel > 20000

This query retrieves objects where the 2014 GDP is greater than 1 trillion and the per capita income is greater than 20,000.

Application of Comparison Operators in Character Fields

Comparison operators such as '>', '<', '>=', '<='. For database data sources, the value of character-type fields must be enclosed in single quotes.

Example: Province_R.Name >= 'Guizhou'

This query retrieves objects in the Province_R dataset where the first letter of the Name field value is between G and Z.

Date Queries

Create a date-type field DataDate for the Province_R dataset to store the statistical time for each record. Taking the query for records where the DataDate field is greater than 2019-08-10 as an example, we illustrate the expression inputs in different database data sources.

  • SQL(SQL+) data source: Province_R.DataDate > '2019-08-10 12:25:00'.
  • ORACLE data source: Province_R.DataDate > TO_DATE('2019-08-10','YYYY-MM-DD').
  • PostGIS data source: "Province_R".datadate > '2019-08-10 12:25:00'.
  • PostgreSQL data source: Province_R.datadate > '2019-08-10 00:00:00'.
  • UDB/UDBX data source: "Province_R".datadate > '2019-08-10', with the date format being YYYY-MM-DD.

Boolean Value Queries

Boolean field attribute values are True or False; in SQL Query, 1 represents True, and 0 represents False.

Example: Province_R.Coastal = 1

This query retrieves objects in the Province_R dataset where the Coastal attribute value is True. Coastal is a newly created Boolean field.

Derived Fields

Example: Province_R.SmArea / Province_R.SmArea

In the query result table, a temporary field will be listed to store the search result. You can rename this field using the 'as' keyword, e.g., Province_R.SmArea / Province_R.SmArea as Pop_Density, where 'Pop_Density' is the temporary field.

If no alias is given, the system uses the expression itself as the temporary field name.

If multiple derived field expressions are specified, they can be separated by commas.

Related Topics

Building SQL Query

Building SQL Expression Query

Function Descriptions for Building SQL Query

Attribute Query Example

Group Statistics Example

Related Query Example