= SQL Expressions =

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

<<TableOfContents>>

----



== Boolean Expressions ==

A '''boolean expression''' can be evaluated for a `WHERE` clause.

The portable relational operators include:

||'''Unary operators'''||'''Meaning'''           ||
||`NOT`                ||Logical NOT             ||

||'''Binary operators'''||'''Meaning'''           ||
||`=`                   ||Equality                ||
||`<>`                  ||Inequality              ||
||`<`                   ||Less than               ||
||`<=`                  ||Less than or equal to   ||
||`>`                   ||Greater than            ||
||`>=`                  ||Greater than or equal to||
||`OR`                  ||Logical OR              ||
||`AND`                 ||Logical AND             ||

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

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

||'''Unary operators'''||'''Meaning'''           ||
||`!`                  ||Logical NOT             ||

||'''Binary operators'''||'''Meaning'''                                           ||
||`!=`                  ||Inequality                                              ||
||`<=>`                 ||Equality, evaluating as true if both operands are `NULL`||
||`<>`                  ||Inequality                                              ||
||`||`                  ||Logical OR                                              ||
||`&&`                  ||Logical AND                                             ||

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%';
}}}

||'''Symbol'''||'''Meaning'''                ||
||`%`         ||Zero or more of any character||
||`_`         ||One of any character         ||

----



== Numeric Expression ==

A '''numeric expression''' evaluates to a numeric scalar.

||'''Function'''||'''Return value'''||
||`AVG`         ||average value     ||
||`COUNT`       ||number of cases   ||
||`MAX`         ||highest value     ||
||`MIN`         ||lowest value      ||
||`SUM`         ||sum of values     ||

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



----
CategoryRicottone