SQL Expressions

In SQL, an expression is a combination of columns, operators, and functions.

Boolean Expressions

A boolean expression can be evaluated for a WHERE clause.

The portable relational operators include:

The logical operators short-circuit except when encountering NULL values.

Certain implementation, such as MariaDB, also support the following:

Strings can be compared. 'Greater' and 'less' than comparisons rely on the locale.

SELECT * FROM table WHERE foo = '2008/06/01';

Dates and times can also be compared.

SELECT * FROM table WHERE date < '2008/06/01';

Expressions can be compounded with AND and OR. They can also be inverted with NOT.

Between

The BETWEEN operator compares one value to a range of values. The range is inclusive. It is equivalent to two expressions utilizing greater than or equal to and less than or equal to.

SELECT foo FROM table WHERE bar BETWEEN 1 AND 3;

In

The IN operator compares one value to each of a set of values. It is equivalent to a series of equality checks compounded with OR.

SELECT foo FROM table WHERE bar IN (1, 2, 3);

The IN operator can also compare a value to another selection of data.

SELECT foo FROM table1 WHERE bar IN (SELECT bar FROM table2);

Like

The LIKE operator compares string values against a pattern.

SELECT foo FROM table WHERE bar LIKE 'a%';

Numeric Expression

A numeric expression evaluates to a numeric scalar.

Except when using COUNT on data that is not subset (i.e. COUNT(*)), NULL values are excluded from all calculations.

CategoryRicottone