# Appendix B: SQL Reference¶

This appendix contains documentation on various aspects of the SQL standard (2016). This is not an exhaustive guide to the standard. Where possible, variations from the standard by various database implementations (SQLite, PostgreSQL, MySQL, Oracle, Microsoft SQL Server) are shown. Note that documentation of this sort is a moving target, so the information below may be out-of-date when you read it. Consult your database vendor’s documentation for current information.

In the documentation below, options may be indicated using square brackets. For example, usage of the SUBSTRING function is documented as

SUBSTRING(*s* FROM *start* [FOR *length*])


meaning both of the following expressions are valid:

SUBSTRING('hello, world' FROM 1 FOR 5)
SUBSTRING('hello, world' FROM 8)


(The first expression evaluates to 'hello', and the second to 'world'.)

## Data types¶

### Number types¶

The SQL standard provides for both exact (integers and fixed-precision decimal) and inexact (floating point) numbers. Details of these types (e.g., the number of bits used in the number representation) are left up to individual implementations, so consult your database’s documentation to fully understand its capabilities.

#### Integers¶

SQL defines three integer types: INTEGER, SMALLINT, and BIGINT. Implementations of these types vary, but it is not uncommon for INTEGER (often abbreviated as INT) to store 32-bit integers, SMALLINT 16-bit integers, and BIGINT 64-bit integers. Not all databases recognize all of these types, but INTEGER is recognized by all of the databases considered for this book. Additional integer types may be available for your database system.

#### Exact decimal numbers¶

Decimal number types allow for exact storage of numbers that have digits to the right of the decimal point, e.g., 1234.56789. These numbers are exact (compare to the floating point types below), and permit exact mathematical operations where possible (addition, subtraction, and multiplication). The two defined types for SQL are NUMERIC and DECIMAL, which are synonyms of each other. These types may be defined with parameters representing precision and scale, where precision is the number of significant digits that can be stored, and scale is the number of digits following the decimal point. If the precision is given, but not the scale, the scale defaults to zero.

For example, in most implementations:

• NUMERIC(3, 2) defines a type that can store the values between -999.99 and 999.99, with a maximum of 2 digits past the decimal point.

• NUMERIC(4) defines a type that can store integers between -9999 and 9999.

• NUMERIC defines a type that can exactly store decimal values with implementation-defined precision and scale.

Different implementations behave differently when an attempt is made to store values with more digits than are allowed by the specified precision and scale. This may result in an error, or (in the case of too many digits to the right of the decimal point), it may result in rounding or truncation of the value.

#### Floating point numbers¶

Floating point number types allow for (possibly inexact) storage of real numbers, similar (or sometimes identical to) the IEEE 754 specification. The SQL standard defines the types FLOAT, REAL, and DOUBLE PRECISION (often abbreviated DOUBLE), but implementation of these types vary.

#### Database support for number types¶

A summary of database support for number types is shown below (for the five databases this textbook attempts to cover):

Type

SQLite

PostgreSQL

MySQL

Oracle

SQL Server

INTEGER

yes

yes

yes

use NUMBER

yes

SMALLINT

equivalent to INTEGER

yes

yes

use NUMBER

yes

BIGINT

equivalent to INTEGER

yes

yes

use NUMBER

yes

NUMERIC/DECIMAL

yes

yes

yes

use NUMBER

yes

FLOAT

equivalent to REAL

equivalent to DOUBLE PRECISION

yes

yes; but BINARY_DOUBLE recommended

yes

REAL

yes

yes

yes

yes; but BINARY_DOUBLE recommended

yes

DOUBLE PRECISION

equivalent to REAL

yes

yes

yes; but BINARY_DOUBLE recommended

use FLOAT

### Character string types¶

There are three main data types for storing character data in SQL. Officially, these are named CHARACTER, CHARACTER VARYING, and CHARACTER LARGE OBJECT. In addition, the modifier NATIONAL may be used to indicate strings containing data from locale-dependent character sets. These names are fairly long and clunky, so databases typically use abbreviations or even completely different names for the same concepts.

The type CHARACTER, usually abbreviated as CHAR, is used for fixed-length strings. The type CHAR is followed by parentheses enclosing the length of the string. All values in a column of type CHAR(4), for example, must contain exactly 4 characters. In practice, many databases relax the “exactly” part of the definition and allow for shorter strings to be stored, although they may “pad” the value with trailing space characters. Attempting to store strings longer than n usually results in an error.

CHARACTER VARYING is usually abbreviated as VARCHAR, and is used for strings of varying length up to some maximum, which must be specified just as with the CHAR type. It is usually an error to attempt to store strings longer than the maximum.

CHARACTER LARGE OBJECT goes by many names, and is used to store strings of arbitrary length, up to some implementation-defined maximum (for example, Oracle’s CLOB type allows strings of up to 128TB in some cases). In many implementations, this type is limited in the operations or functions that may be used, and may not allow indexing.

A summary of database support for character strings is shown below:

Type

SQLite

PostgreSQL

MySQL

Oracle

SQL Server

CHARACTER(n)

equivalent to TEXT

yes

yes

yes

yes

CHARACTER VARYING(n)

equivalent to TEXT

yes

yes

use VARCHAR2(n)

yes

CHARACTER LARGE OBJECT

equivalent to TEXT

use TEXT

use TEXT

use CLOB

use VARCHAR(MAX)

### Date and time types¶

The SQL standard defines three or five principal types, depending on how you count. The types are DATE, TIME (with or without time zone), and TIMESTAMP (with or without time zone). If you specify simply TIME or TIMESTAMP, you get the version without time zones; append WITH TIME ZONE to additionally store time zone information.

• DATE values store dates in such a way that any particular day in history can be accurately recorded. Typically the Gregorian calendar is supported, but some implementations will convert to and from Julian dates or other calendars.

• TIME represents a time of day, without reference to the date. TIME WITH TIME ZONE includes information specifying the time zone relative to which the time should be evaluated.

• TIMESTAMP represents a precise moment in time, incorporating both the date and the time of day (with or without time zone).

A summary of database support for date and time types is shown below:

Type

SQLite

PostgreSQL

MySQL

Oracle

SQL Server

DATE

use TEXT, REAL, or INTEGER

yes

yes

yes

yes

TIME

use TEXT, REAL, or INTEGER

yes

yes

no, use TIMESTAMP

yes

TIME WITH TIME ZONE

use TEXT, REAL, or INTEGER

yes

no

no, use TIMESTAMP WITH TIME ZONE

no

TIMESTAMP

use TEXT, REAL, or INTEGER

yes

yes

yes

use DATETIME2

TIMESTAMP WITH TIME ZONE

use TEXT, REAL, or INTEGER

yes

no

yes

no

In addition to the date and time types, SQL defines a set of types known as interval types, where an interval represents a span of days or time between two date or time values. Intervals are not covered in this book.

## Operators and functions¶

### Comparison operators¶

Generally speaking, two non-NULL values of the same type can be compared, resulting in a Boolean value. In certain cases, comparisons can made between different types, e.g., when both are numbers. Numeric values are compared according to their algebraic values. Date, time, and timestamp values are compared chronologically. The Boolean value True is greater than False.

Character string comparison is somewhat complex, as the comparison done depends on the collation rules in effect for the values; collation may depend on many factors including: the DBMS implementation, DBMS configuration parameters (such as the locale), operating system parameters, and any explicit collation settings for a given database table. Collations may be used to implement proper sorting, for example, in a particular language context. In general, if string s would appear in sorted (ascending) order prior to string t, then s < t.

A comparison of any value with NULL results in NULL 1 when using any of the operators in the table below.

operator

meaning

usage

notes

=

equal to

x = y

<>

not equal to

x <> y

can also use != in most DBMSes (nonstandard)

<

less than

x < y

>

greater than

x > y

<=

less than or equal to

x <= y

>=

greater than or equal to

x >= y

BETWEEN

range comparison

x BETWEEN y AND z

equivalent to x >= y AND x <= z

NOT BETWEEN

exterior range comparison

x NOT BETWEEN y AND z

equivalent to NOT(x BETWEEN y AND z)

Comparison of NULL values requires special treatment; the expression NULL = NULL results in NULL, not True, and thus is not useful in testing for NULL. The IS NULL operator is provided for this purpose. IS NULL (and the inverse, IS NOT NULL) expressions always result in True or False.

Another standard SQL operator that has utility in the presence of NULL values are the binary operators IS DISTINCT FROM and IS NOT DISTINCT FROM. These operators compare two values, treating NULL as if it were a special, distinct value, and always return True or False. Thus, the expression x IS NOT DISTINCT FROM y returns True if x = y evaluates to True or if x and y are both NULL. Of the databases considered for this book, only PostgreSQL implements IS DISTINCT FROM and IS NOT DISTINCT FROM.

The table below summarizes these operators.

operator

usage

result

notes

IS NULL

x IS NULL

True if and only if x evaluates to NULL

IS NOT NULL

x IS NOT NULL

equivalent to NOT (x IS NULL)

IS DISTINCT FROM

x IS DISTINCT FROM y

equivalent to NOT (x IS NOT DISTINCT FROM y)

PostgreSQL only

IS NOT DISTINCT FROM

x IS NOT DISTINCT FROM y

True if x = y is true, or if x and y are both NULL

PostgreSQL only

Also see the Boolean operators section below for comparison operators that only apply to Boolean values.

### Mathematical operators and functions¶

Unless otherwise noted, the operands or parameters below can be any numeric type.

operator/ function

meaning

usage

notes

+

x + y

-

subtraction

x - y

*

multiplication

x * y

/

division

x / y

ABS

absolute value

ABS(x)

MOD

modulus (remainder)

MOD(x, divisor)

integers only in standard SQL

LOG

logarithm

LOG(base, x)

in SQL Server, use LOG(x, base)

LN

natural logarithm

LN(x)

in SQL Server, use LOG(x)

LOG10

base-10 logarithm

LOG10(x)

in Oracle, use LOG(10, x)

EXP

exponential function

EXP(x)

POWER

raise to power

POWER(base, exponent)

SQRT

square root

SQRT(x)

FLOOR

floor function

FLOOR(x)

CEILING

ceiling function

CEILING(x) or CEIL(x)

SIN

sine function

SIN(x)

COS

cosine function

COS(x)

TAN

tangent function

TAN(x)

ASIN

inverse sine

ASIN(x)

ACOS

inverse cosine

ACOS(x)

ATAN

inverse tangent

ATAN(x)

SINH

hyperbolic sine

SINH(x)

COSH

hyperbolic cosine

COSH(x)

TANH

hyperbolic tangent

TANH(x)

Most database implementations provide additional non-standard functions and operators; for example, most include some mechanism for generating random numbers.

Mathematical expressions where one or more operands or inputs are NULL evaluate to NULL.

### Character string operators and functions¶

Below is a partial listing of operators and functions acting on character strings, omitting some less frequently implemented functions and some less frequently used optional parameters.

The SQL standard defines several operators and functions making use of three different pattern-matching languages: the one used by the operator LIKE (discussed in Chapter 3), and two different regular expression (regex) languages; however the databases considered for this book mostly do not conform to the standard with respect to these operators and functions. Many implementations provide functions with similar effect, but under different names and using different regex languages. These functions are therefore omitted, but you are encouraged to read the documentation for your database to see what options are available to you.

operator/function

meaning

usage

notes

||

concatenation

s || t

in MySQL, use CONCAT(s, t); in SQL Server, use s + t

LIKE

pattern comparison

s LIKE pattern

see Chapter 1.3

NOT LIKE

inverse of LIKE

s NOT LIKE pattern

equivalent to NOT (s LIKE pattern)

CHAR_LENGTH

length of string

CHARACTER_LENGTH(s) or CHAR_LENGTH(s)

in SQLite and Oracle, use LENGTH(s); in SQL Server, use LEN(s)

POSITION

index of substring

POSITION(t IN s)

in SQLite and Oracle, use INSTR(s, t)

SUBSTRING

substring extraction

SUBSTRING(s FROM start [FOR length])

in SQLite and Oracle, use SUBSTR(s, start, length); in SQL Server, use SUBSTRING(s, start, length)

UPPER

convert to uppercase

UPPER(s)

LOWER

convert to lowercase

LOWER(s)

TRIM

If t is omitted, whitespace is trimmed; BOTH is the default if LEADING etc. are omitted; in SQLite, Oracle, and SQL Server use LTRIM, RTRIM and TRIM (varying usage)

OVERLAY

substring replacement

OVERLAY(s PLACING t FROM start FOR length)

not in SQLite, Oracle, or SQL Server, but see REPLACE

Most database implementations provide additional non-standard functions and operators.

String operator or function expressions where the operands or inputs are NULL result in NULL.

### Boolean operators¶

The principal Boolean operators in SQL are AND, OR, and NOT. Given operands that are strictly truth valued, i.e., not NULL, these operators result in the logic operations they are named for. That is, a AND b evaluates to True if and only if a and b are both True, c OR d evaluates to True if either c or d are True, and NOT e inverts the value e.

However, since expressions resulting in Boolean values may also result in NULL (e.g., 4 > NULL), NULL is also a valid operand for the Boolean operators, and we can think of SQL as therefore having a 3-valued (rather than truly Boolean) logic 2. The truth tables for AND, OR, and NOT are given below. Treating NULL as meaning “unknown” in Boolean expressions, we can generally infer the result of a Boolean expression involving NULL. For example, True AND NULL must evaluate to NULL (meaning unknown), because the truth of the second operand is unknown. On the other hand, True OR NULL must evaluate to True, as it doesn’t matter whether the second operand represents a true or a false value.

a

b

a AND b

a OR b

True

True

True

True

True

False

False

True

True

NULL

NULL

True

False

True

False

True

False

False

False

False

False

NULL

False

NULL

NULL

True

NULL

True

NULL

False

False

NULL

NULL

NULL

NULL

NULL

a

NOT a

True

False

False

True

NULL

NULL

The SQL standard defines some less frequently used unary operators on Boolean values: IS [NOT] TRUE, IS [NOT] FALSE, and IS [NOT] UNKNOWN, with IS UNKNOWN equivalent to IS NULL except that it only applies to the result of a Boolean expression. So for example, SQL allows us to write NULL < 7 IS FALSE, which would evaluate to False.

SQL Server and Oracle do not implement IS [NOT] TRUE, IS [NOT] FALSE, and IS [NOT] UNKNOWN. SQLite does not implement IS [NOT] UNKNOWN.

Some database implementations provide additional non-standard operators, such as XOR, & as an alternative to AND, etc.

### Date and time operators and functions¶

The SQL standard defines several basic operations relating DATE, TIME (with and without timezone), TIMESTAMP (with and without timezone), and INTERVAL data types. (For a description of these data types, consult the section on Data types above.)

Comparison of like types is accomplished using the Comparison operators previously documented. For example, DATE values can be compared with other DATE values, but not with TIME, TIMESTAMP, or INTERVAL values. (Behavior varies widely among the different database implementations - some do allow comparisons between types not allowed in the SQL standard. However, it is generally inadvisable to compare different types, unless you know exactly how the comparison is being made.)

In addition, the mathematical operators +, -, *, and / may be used as follows:

operator

left operand

right operand

result type

-

DATE, TIME, or TIMESTAMP

DATE, TIME, or TIMESTAMP

INTERVAL

+ or -

DATE, TIME, or TIMESTAMP

INTERVAL

DATE, TIME, or TIMESTAMP

+

INTERVAL

DATE, TIME, or TIMESTAMP

DATE, TIME, or TIMESTAMP

+ or -

INTERVAL

INTERVAL

INTERVAL

* or /

INTERVAL

number (INTEGER, etc.)

INTERVAL

*

number (INTEGER, etc.)

INTERVAL

INTERVAL

So, for example, a subtraction of one TIMESTAMP from another yields an INTERVAL representing the difference in days, hours, minutes, and seconds.

Other operators and functions involving dates and times:

operator or function

meaning

usage

CURRENT_DATE

evaluates to the current date

CURRENT_DATE

CURRENT_TIME

evaluates to the current time

CURRENT_TIME

CURRENT_TIMESTAMP

evaluates to the current date and time

CURRENT_TIMESTAMP

EXTRACT

get a date or time field from a date or time

EXTRACT(field FROM date/time/interval), where field is e.g., ‘YEAR’, ‘HOUR’, etc.

OVERLAPS

test if one span of time overlaps another

period1 OVERLAPS period2, where each period can be (start date/time, end date/time) or (start date/time, interval)

Examples:

EXTRACT('HOUR' FROM TIME '10:03:21') results in the integer 10.

(DATE '2002-07-19', DATE '2003-01-31') OVERLAPS (DATE '2002-12-31', DATE '2005-05-05') results in a True.

In actual practice, the databases considered for this book vary widely in their implementation of the SQL standard in regards to date and time types and operations on those types. The variations are so great, we have not attempted to list departures from the standard in the above tables. In most implementations, similar types can be compared, date and time types can be subtracted to yield intervals, and intervals can be added or subtracted to date and time types to yield a modified date or time. Most databases implement CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP, or something similar. Most implementations provide some function or functions replicating some of the functionality of EXTRACT.

### Miscellaneous operators and functions¶

This section collects some miscellaneous SQL functions that do not fit neatly into the above categories.

function

meaning

usage

notes

NULLIF

NULL if a = b, else a

NULLIF(a, b)

COALESCE

yield first non-NULL argument

COALESCE(a, b, …)

CASE

general purpose conditional expression

see Chapter 1.3

CAST

explicit type conversion

CAST (a AS type)

## Aggregate functions¶

Below is a reference to some of the more commonly implemented aggregate functions defined by the SQL standard. See Chapter 1.9 for a basic guide to their use. The SQL standard also defines a number of statistical functions on two variables; these are implemented in PostgreSQL and Oracle only. Most database implementations provide additional non-standard aggregate functions.

function

meaning

usage

notes

COUNT

count of rows or non-NULL values

COUNT(*) or COUNT([DISTINCT] a)

AVG

average or mean

AVG([DISTINCT] a)

a must be numeric

MAX

maximum

MAX([DISTINCT] a)

MIN

minimum

MIN([DISTINCT] a)

SUM

sum

SUM([DISTINCT] a)

a must be numeric

VAR_POP

population variance

VAR_POP([DISTINCT] a)

a must be numeric; not in SQLite; in SQL Server, use VARP(a)

VAR_SAMP

sample variance

VAR_SAMP([DISTINCT] a)

a must be numeric; not in SQLite; in SQL Server, use VAR(a)

STDDEV_POP

population standard deviation

STDDEV_POP([DISTINCT] a)

a must be numeric; not in SQLite; in SQL Server, use STDEVP(a)

STDDEV_SAMP

sample standard deviation

STDDEV_SAMP([DISTINCT] a)

a must be numeric; not in SQLite; in SQL Server, use STDEV(a)

LISTAGG

concatenate values into a string

LISTAGG(a [, delim])

In SQLite and MySQL, use GROUP_CONCAT(a, delim); in PostgreSQL and SQL Server, use STRING_AGG(a, delim)

Notes

1

Technically, a comparison with NULL results in the value “unknown”. However, in all databases that we are aware of, NULL is indistinguishable from “unknown” except when using the operator IS [NOT] UNKNOWN.

2

See above note. The truth table technically should use “unknown” everywhere “NULL” appears.