Column expressions

Previous Next

In SQL, column expressions are used :

In the select-list of a SELECT statement.

In the VALUES clause of an INSERT statement.

To the right of the = symbol in a SET clause of an UPDATE statement.

To the left and to the right of a comparison operator in a condition.

Syntax

A column expression is a column name, or a literal value, or a composition of columns names and literals connected by function calls or computations:

column-expr      ::=  {

                         {  column  | literal  |  pseudo-column  |  column-expr   |

                            function-call                                         |

                            computation

                         }  column-alias                                          |   

                         column-wildcard

                      }

                     

function-call    ::=  functioncolumn-expr, column-expr... )

 

computation      ::=  column-expr  math-operator  column-expr

 

column           ::=  {table-name|table-alias}.column-name

 

column-wildcard  ::=  {table-name|table-alias}.*

In its simple form, a column expression is a column name, a literal, or a pseudo-column. A special form is the asterisk (*) as column wildcard. This is a shorthand for "all the columns of the table".

A column expression optionally ends with a column alias.

There are two ways to produce composite column expressions: function calls and computations. These productions may be nested to any level deep, and may also be mixed.

A function call consists of the name of an inline function immediately followed by parentheses surrounding zero or more comma-separated column expressions. The parenthesised expressions are passed as input parameters to the function. An inline function (or scalar function) is any supported SQL function other than a group function. Here is an example with nested function calls. This example returns the first word of each title:

SUBSTR( title, 1, INSTR( title, ' ' ) )

In a computation, a mathematical operator is applied to 2 input values. The input values are written as 2 column expressions to the left and to the right of the operator. This example returns the percentage that each sample (from a table s) is of a total population (from a related  table p):

( s.sample / p.total) * 100

 

 

collapseColumn names
collapseColumn wildcard
collapseColumn aliases

 

 

See also

SQL expressions