SQL Operators

This tutorial covers SQL operators.

Similarly to other programming languages, SQL allows the use of operators.

Operators perform specific tasks. These tasks can vary, depending on the type of operator. There are various types of operators.

In this tutorial, we cover the main types of operators.

Arithmetic operators perform mathematical operations.
Comparison operators compare values.
Compound operators perform compound operations.
Logical operators perform testing on conditions.

For more information on operators here.

Let’s use the table “student_info” from the previous tutorials.

id	firstName	lastName	age
1	John	Smith	21
2	Patrick	Casey	26
3	Sam	Adams	19
4	Mike	Garcia	NULL
5	Patrick	Dumont	21

Arithmetic operators

The term arithmetic refers to specific mathematical functions.

In this section, we cover addition (+), subtraction (-), multiplication (*), division (/), and modulo (%).

The following is an example of all arithmetic operators within this page.

SELECT 6 + 3;
-- Output: 9
SELECT 6 - 3;
-- Output: 3
SELECT 6 * 3;
-- Output: 18
SELECT 6 / 3;
-- Output: 2

Modulo refers is a mathematical operation. It simply divides a two numbers and returns the remainder. In other words, it finds the closest (downwards) whole number that can be divided and extracts the remainder as a result.

For instance:

SELECT 6 % 3;
-- Output: 0
SELECT 7 % 3;
-- Output: 1

The first modulo calculation returns 0, as 6 can be divided by 3, leaving no remainder.

The second modulo calculation returns 1, as 7 divided by 3 is simply 6 divided by 3 with the addition of 1 (the remainder).

Comparison operators

The function behind comparison operators is to test whether two expressions match.

For better comprehension, we split these operators into three parts.

The main comparison operators are equals to (=), greater than(>), less than (<).
The combination of comparison operators are greater than or equal to (>=) and less than or equal to (<=).
Negative comparison operators are not equal to (<>), not equal to (!=), not less than (!<), and not greater than (!>).

The exclamation mark (!) sign in many programming languages refers to not equal/negative/reverse. For example, when you add an exclamation mark to “equal to (=)“, it simply converts to “not equal to (!=)“.

Comparison operators work well with the “where” statement. Let’s use the above table “student_info“.

SELECT firstName, age FROM student_info
WHERE age<20;

firstName	age
Sam	19

Only one student matches the condition of under 20 years old.

Let’s try a scenario with a not equal to (<>).

SELECT firstName, age FROM student_info
WHERE age<>21;

firstName	age
Patrick	26
Sam	19

The filter display all students that are not 21 years old. The outcome is a table of 2 students. The student Mike Garcia (id 4) does not output as the age is NULL.

Compound operators

These operators perform tasks onto values, and then sets the original value as the result.

The concept of compound operators is used in many other programming languages.

This section covers add assignment (+=), subtract assignment (-=), multiply assignment (*=), and divide assignment (/=).

Compound operators work well with the “update” statement. Let’s use the above table “student_info“.

UPDATE student_info
SET age+=10;

5 rows affected. The above increments all ages inside the table by 5.

Now we can display the changes.

age (before)	age (after)
21	31
26	36
19	29
NULL	NULL
21	31

Logical operators

The concept of logical operators can be found in many programming languages.

The function of these operators is to test whether a specific condition is True or False.

This section covers only:

and (True if both conditions are true)
or (True if at least one condition is true)
not (reverses the operator to Not True)

More logical operators will be covered in more details separately (check further tutorials).

We are using the original “student_info” table, without the age increment.

SELECT * FROM student_info
WHERE firstName='Patrick' and age=26;

id	firstName	lastName	age
2	Patrick	Casey	26

Although the table consist of two student records containing the name Patrick, only one is 26 years old. Therefore, the above statement displays only one student (id 2) matching both conditions.

Next: SQL Between