SQL Pocket Guide, 4th Edition

Chapter 7. Operators and Functions

Operators and functions are used to perform calculations, comparisons, and transformations within a SQL statement. This chapter provides code examples for commonly used operators and functions.

The following query highlights five operators (+, =, OR, BETWEEN, AND) and two functions (UPPER, YEAR):

-- Pay increases for employees
SELECT name, pay_rate + 5 AS new_pay_rate
FROM employees
WHERE UPPER(title) = 'ANALYST'
      OR YEAR(start_date) BETWEEN 2016 AND 2018;

Operators Versus Functions

Operators are symbols or keywords that perform a calculation or comparison. Operators can be found within the SELECT, ON, WHERE, and HAVING clauses of a query.

Functions take in zero or more inputs, apply a calculation or transformation, and output a value. Functions can be found within the SELECT, WHERE and HAVING clauses of a query.

In addition to SELECT statements, operators and functions can also be used in INSERT, UPDATE, and DELETE statements.

This chapter includes one section on Operators and five sections on functions: Aggregate Functions, Numeric Functions, String Functions, Date Time Functions, and Null Functions.

Table 7-1 lists common operators and Table 7-2 lists common functions.

Table 7-1. Common operators

Logical Operators	Comparison Operators (Symbols)	Comparison Operators (Keywords)	Math Operators
AND OR NOT	= !=, <> < <= > v=	BETWEEN EXISTS IN IS NULL LIKE	+ - * / %

Table 7-2. Common functions

Aggregate Functions	Numeric Functions ...