File geodatabases SQL reference

You can use SQL queries to access and analyze data in a file geodatabase.

The following sections provide descriptions and examples of SQL expressions, functions, operators, keywords, and clauses that can be used to query tables, feature classes, and views in file geodatabases.

Alias

You can use the AS keyword to specify an alias for a field name, table name, or the values returned by the SQL query. This provides a more understandable output.

In the following example of using the AS keyword, the results of subtracting the values in the POP1990 column from the values in the POP1997 column are returned under the alias PopChange.

SELECT COUNTY_NAME, POP1997 - POP1990 AS PopChange
 FROM counties
 ORDER BY COUNTY_NAME

CASE expressions

A CASE expression evaluates a list of specified conditional expressions and returns results based on those expressions.

The following statement includes a set of conditions in a CASE expression that returns the words low, average, or high depending on salary values.

SELECT name,salary,
 CASE
	 WHEN salary <= 2000 THEN 'low'
	 WHEN salary > 2000 AND salary <= 3000 THEN 'average'
	 WHEN salary > 3000 THEN 'high'
	END AS salary_level
	FROM employees

Functions

The following is a list of functions supported by file geodatabases.

Date functions

Date functions return date and time values.

CURRENT_DATE	Returns the current date provided by the operating system of the machine where you run the SQL query.
EXTRACT (extract_field FROM extract_source)	Returns the portion of the date-time expression (denoted by extract_source) based on the keyword specified for the extract_field argument. The extract_field argument can be one of the following keywords: YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND.
CURRENT TIME	Returns the current time provided by the operating system of the machine where you run the SQL query.
CURRENT_TIMESTAMP	Returns the current time and date provided by the operating system of the machine where you run the SQL query.

Numeric functions

Numeric functions perform operations on numeric values. All numeric functions return a numeric value.

Arguments denoted as numeric_exp, float_exp, or integer_exp can be the name of a column, the result of another scalar function, or a numeric-literal, for which the underlying data type could be represented as a numeric type.

ABS (numeric_exp)	Returns the absolute value of the number denoted by the numeric_exp argument.
ACOS (float_exp)	Returns the arccosine value of the number denoted by the float_exp argument. The returned value is an angle, expressed in radians.
ASIN (float_exp)	Returns the arcsine value of the number denoted by the float_exp argument. The returned value is an angle, expressed in radians.
ATAN (float_exp)	Returns the arctangent value of the number denoted by the float_exp argument. The returned value is an angle, expressed in radians.
CEILING (numeric_exp)	Returns the smallest integer greater than or equal to the value denoted by the numeric_exp argument.
COS (float_exp)	Returns the cosine value of the angle (in radians) denoted by the float_exp argument.
FLOOR (numeric_exp)	Returns the largest integer less than or equal to the number denoted by the numeric_exp argument.
LOG (float_exp)	Returns the natural logarithm of the value denoted by the float_exp argument.
LOG10 (float_exp)	Returns the base 10 logarithm of the value denoted by the float_exp argument.
MOD (integer_exp1, integer_exp2)	Divides the integer_exp1 value by the integer_exp2 value and returns the remainder.
POWER (numeric_exp, integer_exp)	Returns the value of numeric_exp to the power of integer_exp.
ROUND (numeric_exp, integer_exp)	Returns the numeric_exp value rounded to the number of places to the right of the decimal point as denoted by the integer_exp argument. If the value denoted by integer_exp is negative, the numeric_exp value is rounded to |integer_exp| places to the left of the decimal point.
SIGN (numeric_exp)	Returns an indicator of the sign (negative, positive, or zero) of the value denoted by the numeric_exp argument. If the numeric_exp value is less than zero, -1 is returned. If the numeric_exp value equals zero, 0 is returned. If the numeric_exp value is greater than zero, 1 is returned.
SIN (float_exp)	Returns the sine of the float_exp value, where float_exp is an angle expressed in radians.
TAN (float_exp)	Returns the tangent of the float_exp value, where float_exp is an angle expressed in radians.
TRUNCATE (numeric_exp, integer_exp)	Returns the numeric_exp value truncated to the number of places to the right of the decimal point denoted by the integer_exp argument. If the integer_exp value is negative, the numeric_exp value is truncated to |integer_exp| places to the left of the decimal point.

String functions

String functions perform operations on string values (text characters).

Arguments denoted as string_exp can be the name of a column, a character-string-literal, or the result of another scalar function, where the underlying data type can be represented as a character type.

Arguments denoted as character_exp are variable-length character strings.

Arguments denoted as start or length can be a numeric-literal or the result of another scalar function, where the underlying data type can be represented as a numeric type.

These string functions are 1-based; that is, the first character in the string is character 1.

CHAR_LENGTH (string_exp)	Returns the length, in characters, of the string expression.
CONCAT (string_exp1, string_exp2)	Returns a character string that is the result of concatenating the text in string_exp2 to the text in string_exp1.
LOWER (string_exp)	Returns a string equal to that in the string_exp argument, with all uppercase characters converted to lowercase.
POSITION (character_exp IN character_exp)	Returns the position of the first character expression in the second character expression. The result is an exact numeric with an implementation-defined precision and a scale of zero.
SUBSTRING (string_exp FROM start FOR length)	Returns a character string that is derived from the text represented by the string_exp argument, beginning at the character position specified by the start argument and continuing for the number of characters specified for the length argument.
TRIM (BOTH | LEADING | TRAILING trim_character FROM string_exp)	Returns the text specified for the string_exp argument, with the character specified by the trim_character argument removed from the leading, trailing, or both ends of the string.
UPPER (string_exp)	Returns a string equal to that in the string_exp argument, with all lowercase characters converted to uppercase.

Aggregate functions

Use an aggregate function to perform a calculation on a specified set of values, to return a single value that is a result of that calculation.

AVG	Calculates the average of all values in the set. Null values are ignored.
COUNT(*), COUNT(expression)	COUNT(*) returns the number of records in a table. Nulls are included. COUNT(expression) returns the number of values in the specified expression. Nulls are ignored.
MAX	Returns the maximum value in the set. Null values are ignored.
MIN	Returns the minimum value in the set. Null values are ignored.
STDDEV, STDDEV_SAMP	Returns the sample standard deviation of the expression.
STDDEV_POP	Returns the population standard deviation of the expression.
SUM	Adds the values in the set and returns the resultant sum. Null values are ignored.
VAR, VAR_SAMP	Returns the sample variance of the expression.
VAR_POP	Returns the population variance of the expression.

COALESCE

The COALESCE function returns the first non-null field value among its arguments.

SELECT Name, COALESCE(Business_Phone, Cell_Phone, Home_Phone)
Contact_Phone
 FROM Contact_Info

The following is a sample of records from the Contact_Phone table in the geodatabase:

Name	Business_Phone	Cell_Phone	Home_Phone
Jeff	531-2531	622-7813	565-9901
Laura	NULL	772-5588	312-4088
Peter	NULL	NULL	594-7477

Including the COALESCE function in the expression results in the following output:

Name	Contact_Phone
Jeff	531-2531
Laura	772-5588
Peter	594-7477

NULLIF

The NULLIF function returns NULL if the two parameters provided are equal; otherwise, the value of the first parameter is returned.

SELECT Location, NULLIF(Sales, Forecast) AS Results
 FROM StoreSales

The following is an example of values in the location, sales, and forecast fields of the StoreSales table:

Location	Sales	Forecast
Redlands	39000	55000
Palm Springs	60000	61000
Riverside	40000	40000

Running the expression with a NULLIF function on the StoreSales table returns the following results:

Location	Results
Redlands	39000
Palm Springs	60000
Riverside	NULL

Clauses

Clauses work as filters that restrict, order, or modify the results of a query expression.

GROUP BY

The GROUP BY clause is used to collect data across multiple records and group the results by one or more columns.

The following statement summarizes (adds) all values in the POP1990 field in the counties table, and groups the values by state. Values are also returned in ascending alphabetical order by state name.

SELECT state_name, SUM(POP1990) AS TotalPopulation
 FROM counties
 GROUP BY STATE_NAME
 ORDER BY STATE_NAME

GROUP BY clauses can include aggregation functions such as MIN, MAX, and SUM.

HAVING

Use the HAVING clause with aggregate functions in place of a WHERE clause.

In the following example, the MAX aggregate function is used to return the highest salary stored in the employees table. The HAVING clause restricts that value to the maximum value below 50000.

SELECT department, MAX(salary) AS Highest_salary
 FROM employees
 GROUP BY department HAVING MAX(salary) < 50000

JOIN clauses

JOIN clauses return the combined records from two or more tables.

CROSS JOIN

The following example returns every row in table1 matched with every row in table2 and every row in table2 matched with every row in table1. If table1 contains 100 rows and table2 contains 1,000 rows, 100,000 rows will be returned. This type of join should be used carefully, as the number of returned rows will get large fast.

SELECT Table1.name, Table1.Address, Table2.name, Table2.Salary
 FROM Table1
  CROSS JOIN Table2

INNER JOIN

The following statement returns the rows from both tables when the key values (Table1.C1, Table2.C3) match. No other rows will be returned from either table.

SELECT Table1.C1, Table1.C2, Table2.C3, Table2.C4
 FROM Table1
  INNER JOIN Table2 ON Table1.C1 = Table2.C3

LEFT OUTER JOIN

The following statement returns all of the rows from the left table (Table1) and only the rows from the right table (Table2) when the key values match (Table1.C1, Table2.C3).

SELECT Table1.C1, Table1.C2, Table2.C3, Table2.C4
 FROM Table1
  LEFT OUTER JOIN Table2 ON Table1.C1 = Table2.C3

RIGHT OUTER JOIN

This statement returns all of the rows from the right table (Table2) and only the rows from the left table (Table1) when the key values match (Table1.C1, Table2.C3).

SELECT * FROM Table1
 RIGHT OUTER JOIN Table2 ON Table1.C1 = Table2.C3

ORDER BY

The ORDER BY clause specifies sort order. The order can be ascending (ASC) or descending (DESC). By default, values are sorted in ascending order.

You can also specify a collation when you use the ORDER BY clause. Collation type arguments include the following:

• BINARY(BIN)—Binary collation is both case and accent sensitive. When you use this collation argument, lowercase letters sort ahead of their uppercase versions when sorting in ascending order, and accents are sorted ahead of letters when sorting in ascending order. You can specify either BINARY or BIN in the query.
• CASESENSITIVE(CASE)—A case-sensitive collation distinguishes between uppercase and lowercase letters. When you use this collation argument, lowercase letters sort ahead of their uppercase versions. You can specify either CASESENSITIVE or CASE in the query.
• NOCASESENSITIVE(NOCASE)—The NOCASE argument does not distinguish between uppercase and lowercase letters, which means that case is ignored for the sort order. You can specify either NOCASESENSITIVE or NOCASE in the query.

Note:

You cannot use an ORDER BY clause when defining a view.

The following are examples of using the ORDER BY clause:

Values will be returned in ascending alphabetical order by state name.

SELECT STATE_NAME, POP1990
 FROM counties
 ORDER BY STATE_NAME

Values will be returned in descending alphabetical order by state name, with uppercase names returned first.

SELECT STATE_NAME, POP1990
 FROM counties
 ORDER BY STATE_NAME
 COLLATE CASE DESC

Operators

The following operators are supported by file geodatabases.

Arithmetic operators

Use an arithmetic operator to add, subtract, multiply, and divide numeric values.

*	Arithmetic operator for multiplication
/	Arithmetic operator for division
+	Arithmetic operator for addition
-	Arithmetic operator for subtraction

SIMILAR TO

SIMILAR TO is a comparison operator that evaluates string values and returns true or false.

The following example query evaluates the values in the state_name field and returns true if the word North appears at the start of any values in that field or returns false if no value in the state_name field starts with the word North.

SELECT state_name
 FROM counties
 WHERE state_name SIMILAR TO 'North%'

Note:

Values that begin with the word north (with a lowercase n) will return false for the example above because the string comparison is case sensitive.