This tutorial will discuss the four arithmetic operators in Standard SQL and how to use them.
SQL Arithmetic Operators
In Standard SQL, there are four main types of arithmetic operators. They include:
- Addition operator (+)
- Subtraction operator (-)
- Multiplication operator (*)
- Division operator (/)
Using SQL Arithmetic operators
Using arithmetic operators in SQL is pretty straightforward. Moreover, it conforms to the rules of basic arithmetics.
However, we can express the general syntax as shown in the example below:
In this case, the left and right operands refer to either a single numeric value or an expression that evaluates a numerical result.
Let us discuss various examples for using arithmetic operators in SQL.
To perform an addition operation in SQL, we can run an example as shown:
We add two operands together in the above query using the addition operator. The resulting set is as shown:
In more realistic scenarios, you can add values from a database as shown in the example query below:
The example above shows you can use arithmetic operators to calculate the total price of items inclusive of tax.
There is not much difference between the usage of the addition and subtraction operator in SQL. For example:
The above query should return the difference between the specified operands.
The same case goes for the multiplication and division operators. An example usage is as shown below:
SELECT 454 / 45 as quotient;
The above should return the product and quotient of the specified operands, respectively.
There is another type of operator in SQL. This may not be fully supported, depending on the database engine.
However, as of writing this article, most relational database engines support it.
The modulo operator is denoted with a % sign and returns the remainder of the division between two operands.
An example is as shown:
This should return the value as:
This article explored how to use basic arithmetic operators in Standard SQL. Keep practicing and check our tutorials to learn more.