Database Wisdom: SQL - Data Types

Expressions and Operators

In SQL (and other programming languages), an expression is nothing more than a statement that returns a value.

1 + 2

A + B

Column1 + column2

Column1 || Column2

SQLFunction(column1)

Column1 = Column2

The above are all expressions. When we talk about expressions, we usually talk about the type of expression, a character expression, boolean expression, numeric expression or date/time expression.

1 + 2 is a numeric expression. We know this because both input values are numeric and the results of the + operator on two numeric values is a numeric output. More on operators below.

In the case of A + B, we can't be positive that we know what type of expression that is. We would either need to see the definitions of A and B or we would need to see the result of the expression. We can make a good guess based on the operator is uses, though. The + operator tells me that it is probably a numeric expression; it is adding two numeric values. Technically, A could be a date and B could be a numeric, in which case the expression would return a date value, making it a date expression. Less likely, it also might possibly be a string expression.

The case of Column1 = Column2 is a boolean expression. The = operator always returns a boolean result.

In SQL, you can use expressions almost anywhere in a statement. A column in a select list is an expression (more on that below). A function call is an expression. The components of a WHERE clause are expressions.

Through out the book, I will use the term expression. When I do, I mean a statement that will return a value.

On the topic of operators, there are many available to a SQL developer. All of the expected mathematical operators are available: +, *, -, /, %, (), [], {}, etc. Normal mathematical operator precedence is in effect in SQL.

Logical, i.e. boolean, operators include: AND, OR, =, <>, >, <, <=, and >=.

I speak more on some of these operators below in the section on viewing data.

Topics:

• Intro
• SQL
• DML
• DDL
• Data Types
• Expressions and Operators
• Common SQL Functions