7.SQL operators

SQL Arithmetic Operators

Operator

Operators are specific words or characters that help you to perform different activities in a database. They’re like conjunctions or connection words you’d use to compose a sentence. Or the operation keys used to perform a sum in a calculator.

SQL operators allow you to manipulate data as necessary to perform these different activities in the database.

Arithmetic operators

Arithmetic operators are commonly used in computer languages to perform a calculation and return the result. Much like common arithmetic operators in mathematics, you can use arithmetic operators in SQL to carry out mathematical operations in a database.

The SQL arithmetic operators and their symbols are:

• Plus for addition
• Minus for subtraction
• Asterisk for multiplication
• Forward slash for division
• Percentage for modulus : which provides the remainder value of a division calculation.

How it works

When performing a calculation, an operator takes two operands and returns a result.

In SQL, you can apply the same concept by using the select command for the various operations.

Let’s illustrate this concept using the addition operator.

You can use the select command followed by one operand, the addition operator, and the second operand. Just like the previous example, SQL calculates the two operands and produces the result.

Select 5 + 5 = 10
Select 5 - 5 = 0
Select 5 * 5 = 25
Select 5 / 5 = 1
Select 5 % 5 = 0

SELECT 10 + 15;

The query produces the result of the example addition operation, which is 25.

10 + 15
25

---

SQL Arithmetic Operator Examples

In this reading, you’ll learn more about the arithmetic operators that can be used with SQL. You’ve learned about the arithmetic operators in SQL that are used to perform basic mathematical operations such as addition, subtraction, multiplication and division. You’ve also explored the modulus operator, which gives the remainder of a mathematical division. The main objective of this reading is to present some more examples of how arithmetic operators can be used. It also includes more advanced scenarios.  

Arithmetic operators

Arithmetic operators are useful when you want to perform mathematical operations on the data in tables while you retrieve them by writing SQL SELECT queries. In SQL, arithmetic operators are used to perform mathematical operations on data. To be more specific, they’re used with numerical data stored in database tables.

Arithmetic operators can be used in the SELECT clause as well as in the WHERE clause in a SQL SELECT statement. When an operator is used in the WHERE clause, it’s intended to perform the operations on specific rows only. This is because the WHERE clause in SQL is used to filter out data that a particular SQL statement is working on.

All arithmetic operators are used on numerical operands for performing:

• Addition 

• Subtraction 

• Multiplication 

• Division 

• Modulus 

Using the addition operator

The SQL addition operator performs the mathematical addition operation on numerical data within columns in a table. For example, if you want to add the values of two instances of numerical data from two separate columns in the table, then you need to specify the two columns as the first and second operand. The syntax is as follows:

SELECT column_name1 + column_name2 FROM table_name;

Let’s review an example. This is an employee table from a company database. 

employee_ID	employee_name	salary	allowance
1	Alex	25000	1000
2	John	55000	1000
3	James	52000	1000
4	Sam	30000	1000

If you want to know the total salaries of all employees with the basic salary and the allowance added to it then you can use the addition operator. The SQL syntax for the addition operator is as follows:

SELECT salary + allowance FROM employee;

Here, the salary column and the allowance column are the two operands. The addition operator is used to add the values of these twocolumns together. The output is as follows: 

Salary + allowance
26000
56000
53000
31000

Here’s an example of the addition operator in the WHERE clause using the data in the following employee table: 

employee_ID	employee_name	salary	allowance
1	Alex	24000	1000
2	John	55000	1000
3	James	52000	1000
4	Sam	24000	1000

Let’s say you want to retrieve the salaries of employees whose total salary is 25000. This is how you can use the addition operator in SQL:

SELECT * 
 
FROM employee 
 
WHERE salary + allowance = 25000; 

The SQL statement filters the records of all employees whose total salary (salary plus allowance) is 25000.

The output displays the records of two employees with ID 1 and 4 as in the table below.

employee_ID	employee_name	salary	allowance
1	Alex	24000	1000
4	Sam	24000	1000

Using the subtraction operator

The SQL subtraction operator performs mathematical subtraction on numerical data within columns in a database table. If you want to subtract the values of one numerical column from the values of another numerical column, you must specify both columns as the first and second operands along with the subtraction operator. The syntax is as follows:

SELECT column_name1 - column_name2 FROM table_name;

Let’s examine an example. Here’s the employee table once again, but this time with a “Tax” column and several instances of new data. 

employee_ID	employee_name	salary	allowance	tax
1	Alex	24000	1000	1000
2	John	55000	1000	2000
3	James	52000	1000	2000
4	Sam	24000	1000	1000

Let’s say you want to retrieve the salaries of employees after deducting tax. This is the SQL syntax that you can use with the subtraction operator to get these results.

SELECT salary - tax FROM employee;

Here, the salary and tax columns are the operands, and the subtraction operator is applied to them. The values in the tax column are deducted from the values in the salary column. The output is as follows:

salary – tax
23000
53000
50000
23000
23000

Here’s an example of using the subtraction operator in the WHERE clause. Consider the same employee table and data. If you want to find out who earns a salary of 50000 after the tax deduction, this is the SQL query you can write:

SELECT * 
 
FROM employee 
 
WHERE salary - tax = 50000; 

Here, you are filtering out employees who receive a salary of 50000 after tax in the WHERE clause. This is the result that you would get. 

employee_ID	employee_name	salary	allowance	tax
3	James	52000	1000	2000

Using the multiplication operator

The SQL multiplication operator performs the mathematical multiplication operation on the numerical data typed columns in a database table. If you want to multiply the values of two numerical columns, you must specify both columns as the first and second operand with the multiplication operator between them.

Let’s say in the employee table, you want to generate the tax amounts for each employee if these amounts are doubled.

You would write a SQL SELECT statement like this.

SELECT tax * 2 FROM employee;

Here, you are doubling the tax for all employees by multiplying the tax column value by 2.

The result would be as follows:

tax * 2
2000
4000
4000
2000

Now let’s review an example of how to use the multiplication operator in the WHERE clause.Let’s say you want to know who must pay an amount of tax equal to 4000, after doubling the current tax value.

The SELECT query gives the desired result, using the multiplication operator in the WHERE clause.

SELECT *  
 
FROM employee 
 
WHERE tax * 2 = 4000; 

Here, the WHERE clause filters out the employees’ records. It shows who’ll be paying tax amounting to 4000 after the current tax amount is doubled. The result is as follows: 

employee_ID	employee_name	salary	allowance	tax
2	John	55000	1000	2000
3	James	52000	1000	2000

Using the division operator

The division operator divides the numerical values of one column by the numerical values of another column. The syntax of using the division operator is as follows:

SELECT column_name1 Division_Operator column_name2 FROM table_name;  

The data in the employee table is as follows:

employee_id	employee_name	salary	allowance	tax
1	alex	24000	1000	1000
2	John	55000	3000	2000
3	James	52000	3000	2000
4	Sam	24000	1000	1000

In this next example, let’s say that you want to find out the allowance percentage each employee receives, by using the salary and the allowance amount.

You can write a SQL SELECT statement with the division operator as follows:

SELECT allowance / salary * 100 FROM employee;

Here, both the division and multiplication operators are used together to divide the allowance by salary and multiply the answer by 100 to find out the allowance percentage.

The result is as follows:

allowance / salary * 100
4.1667
5.4545
5.7692
4.1667

Like the other arithmetic operators, this too can be used in the WHERE clause of a SELECT statement.Let’s say you want to find out which of the employees get an allowance of at least 5%.

Here’s how the division operation is used in the WHERE clause to achieve this:

SELECT *  
 
FROM employee 
 
WHERE allowance / salary * 100 >= 5; 

Both the division and the multiplication operators are used in the WHERE clause to filter out the employees who receive an allowance of at least 5% of their salary. The output is as follows:

employee_ID	employee_name	salary	allowance	tax
2	John	55000	3000	2000
3	James	52000	3000	2000

Using the modulus operator

The modulus operator (%) behaves as it’s expected in SQL by giving the remainder when the numerical values of one column is divided by the numerical values of another column. The syntax is as follows:

SELECT column_name1 % column_name2 FROM table_name;

In this example, you’re working with the following data in the employee table.

employee_ID	employee_name	salary	hours	allowance	tax
1	alex	24000	10	1000	1000
2	John	55000	11	3000	2000
3	James	52000	7	3000	2000
4	Sam	24000	11	1000	1000

You want to find out if the number of hours worked by each employee is an even number. You can find this out by issuing the following SQL statement:

SELECT hours % 2 FROM employee;

Here, the modulus operator is applied to the hours column to see if there’s a remainder when it’s divided by 2. If the remainder is zero, that means the number of hours worked by that employee is an even number. If the remainder is greater than 0, that means the number of hours worked by that employee is an odd number. 

In this case, the output is as follows:

salary % hours
0
1
1
1

As the output shows, only employee 1 has worked for an even number of hours. The rest of the employees have worked for an odd number of hours. If you want to filter out only the employees who worked for an even number of hours, use the following SELECT statement that uses the modulus operator in the WHERE clause:

SELECT * 
 
FROM employee 
 
WHERE hours % 2 = 0; 

The result of this SQL statement shows the employee record with id 1 as shown in the following table: 

employee_ID	employee_name	salary	hours	allowance	tax
1	alex	24000	10	1000	1000

In this reading, you learned more about the use of arithmetic operators in SQL including addition, subtraction, multiplication, division, and modulus. The examples given should help you to understand how these operators can be used in the SELECT and the WHERE clauses.

---

Operators in use

SELECT salary + 500 FROM employee

SELECT salary - 500 FROM employee

---

SQL Comparison operators

Comparison operator

Comparison operators are used to compare two values or expressions where the outcome result can be either true or false. They can be used to filter data and to include and exclude data.

Sequel uses common mathematical comparison operators by means of the symbols equal to less than and greater than.

It also uses less than or equal to greater than or equal to and not equal to.

SELECT * FROM employee WHERE salary = 18000;

SELECT * FROM employee WHERE salary < 24000;

SELECT * FROM employee WHERE salary <= 24000;

In this final example, The employer wants to know which employees receive a salary that is not equal to $24,000 per year.

SELECT * FROM employee WHERE salary <> 24000;

---

SQL Comparison operator examples

In this reading you’ll learn more about SQL comparison operators. You’ve learned about comparison operators in SQL that are used to form conditions for filtering data. Comparison operators are useful when you want to write conditions in the WHERE clause of a SELECT statement to filter out records from a table.The main objective of this reading is to present some more examples of how those comparison operators can be used, along with more advanced scenarios. 

Refreshing SQL comparison operators

As you’ve already learned, all the comparison operators that are used with other programming languages also work with SQL.

These comparison operators are:

Operator	What it does
=	Checks for equality
<>  or !=	Checks for not inequality
>	Check if something is greater than
>=	Check if something is greater than or equal
<	Check if something is less than
⇐	Check if something is less than or equal

You’ve seen examples of how to use these comparison operators already but let’s examine a few more scenarios here.

Using the equality operator

You can use the = operator to test for equality in a query. It compares the equality of two expressions. The equal operator is used in the WHERE clause condition of a SELECT statement.

Let’s consider this example table and its data.

employee_ID	employee_name	salary	hours	allowance	tax
1	alex	24000	10	1000	1000
2	John	55000	11	3000	2000
3	James	52000	7	3000	2000
4	Sam	24000	11	1000	1000

If you want to retrieve the data for the employee whose ID value is 1, you can use this SELECT statement.

SELECT * FROM employee WHERE employee_id = 1;

This statement filters the record of the employee whose ID column has a value that is equal to 1. The result is as follows: 

employee_ID	employee_name	salary	hours	allowance	tax
1	Alex	24000	10	1000	1000

That was an example of using the equality operator with a numeric data typed column. Now let’s review an example of using the equality operator with a text-based data typed column, the employee_name. 

In this example, you need to retrieve the data for the employee whose name is James. You can use the equal operator in the WHERE clause condition.

SELECT * FROM employee WHERE employee_name = 'James';

The important thing to note here is that the value or literal, in this case James, is surrounded by single quotation marks.

Using the inequality operator

The inequality operator does the opposite of what the equal operator does. It compares two non-null expressions and returns true if the value of the left expression is not equal to the right one. If not, it returns the value of false.

There are two ways in SQL in which it can be used, <> or != and both methods result in the same outcome. 

For example, let’s say you want to determine which employee receives a salary that does not equate to 24000. You can use the following SQL statement:

SELECT *  
 
FROM employee 
 
WHERE salary <> 24000; 

The expression here is salary <> 24000. This expression checks the inequality of the given value in the salary column and filters out only those records. The result here are as follows: 

employee_ID	employee_name	salary	hours	allowance	tax
2	John	55000	11	3000	2000
3	James	52000	7	3000	2000

You can run the same query just by replacing <> with !=. It should behave in the same way and give the same result.

Using the greater than operator

This comparison operator compares two non-null expressions and returns true if the left operand is greater than the right operand. If not, the result is false. Let’s say you want to find out which employees are earning a salary of over 50000. You would write the SQL SELECT query as follows using the greater than comparison operator.

SELECT *  
 
FROM employee 
 
WHERE salary > 50000; 

The expression here is salary > 50000. This expression checks if the value of the salary column is greater than 50000. If so, it includes those employees in the results.

Accordingly, the result is:

employee_ID	employee_name	salary	hours	allowance	tax
2	John	55000	11	3000	2000
3	James	52000	7	3000	2000

Using the greater than or equal operator

The greater than or equal operator (>=) compares two non-null expressions. The result is true if the left expression is a value that is greater than the value of the right expression. This time let’s say you want to see who pays a tax amount of 2000 or more. This is the query that will give the desired result using the greater than or equal operator:

SELECT *  
 
FROM employee 
 
WHERE tax >= 1000;  

The expression here is tax >= 1000 and it checks if the tax column value is greater than or equal to 1000. If it finds any matching records, these are added to the result set. 

The result in this case is as follows:

employee_ID	employee_name	salary	hours	allowance	tax
1	Alex	24000	10	1000	1000
2	John	55000	11	3000	2000
3	James	52000	7	3000	2000
4	Sam	24000	11	1000	1000

All 4 records are included in the result because the SQL query matches the tax column and picks the rows that have a value of 1000 or more.

Using the less than operator

The < operator in SQL can be used to test for an expression which is less than. That is, it compares two non-null expressions. The result is true if the left operand evaluates to a value that is lower than the value of the right operand. If not, the result is false. 

For example, let’s say you want to determine which employees get an allowance below 2500.

The following SQL query can be used to retrieve the result.

SELECT *  
 
FROM employee 
 
WHERE allowance < 2500; 

The allowance < 2500 expression checks the values of the allowance column to determine which ones have a value of less than 2500. 

The result is as follows:

employee_ID	employee_name	salary	hours	allowance	tax
1	alex	24000	10	1000	1000
4	Sam	24000	11	1000	1000

Using the less than or equal operator

In SQL, the ⇐ operator tests for an expression less than or equal to. That is, it compares two non-null expressions and returns true if the left expression has a value less than or equal to the value of the right expression. If not, it returns true. 

You’d use it, for example, if you want to determine which employees have worked for less than or equal to 10 hours. The following syntax is an example of how you would use the less than or equal operator in the WHERE clause of the SELECT statement:

SELECT *  
 
FROM employee 
 
WHERE hours <= 10; 

The expression hours ⇐ 10checks the values of the hours column to see if there are any records with a value of 10 less than that. If it finds one, it adds it to the result.

Accordingly, the result of this query is as follows:

employee_ID	employee_name	salary	hours	allowance	tax
1	Alex	24000	10	1000	1000
3	James	52000	7	3000	2000

---

Additional resources

The following resources are additional reading material that provide extra knowledge on SQL Arithmetic operators and Comparison operators.

• W3Schools
• Javatpoint
• Tutorialspoint
• w3resource

---

SQLDataDatabase

Previous one → 6.Update and delete | Next one → 8.Sorting and filtering data