SQL Operators Explained

In this simple guide, we are going to look at SQL Operators, explaining the concepts and use cases of logical, comparison and arithmetic functions that can be performed in SQL.

This tutorial is part of the SQL Made Easy series of posts. In this post we will discuss:

• When would we use operators?
• What exactly are operators?
• Comparion Operators
• Logical Operators
• Arithmetic Operators

When would we use operators?

When working with large databases in SQL, sometimes we will need to make calculations in our queries in order to best display the information in a specific way.

This can typically be done in two key ways:

• Filtering data based on a specific criteria
• Adding additional columns to be displayed, filled with data based on existing columns

What exactly are SQL operators?

Put simply – a SQL Operator is a basic phrase or symbol that is added to a query in order to achieve some kind of result such as a filter or modified data output.

For example – within an arithmetic operator it is as simple as including a + symbol to represent the addition of two values, or the word BETWEEN when working with logical operators and determining a range of values to filter to such as customers who made between 10 and 20 purchases.

To make this more clear, let’s look at the three most common types of SQL Operators in detail – Logical, Comparison and Arithmetic.

Logical Operators

Logical Operators tend to be the most commonly used of the SQL Operators. They work as a filter to narrow down the query results.

Below are the most used examples of Logical Operators:

Logical Operator Examples

Let’s look at a couple of quick examples of Logical Operators in action.

For the examples used in this tutorial we are going to look at a dataset of top selling video games that is available on Kaggle.

Let’s say we only care about seeing the top selling games in the Sports and Racing genres. We could use a simple IN condition to filter to this as below:

SELECT Name, Genre
FROM vgsales
WHERE Genre IN ('Sports', 'Racing')
LIMIT 10;

Result:

Next up, what if we wanted to see what the top selling Mario games are? Here we can use the LIKE condition to search for the characters ‘Mario’ within a name:

SELECT Name, Global_Sales
FROM vgsales
WHERE Name LIKE '%Mario%'
LIMIT 10;

Result

Comparison Operators

Comparison Operators, like the name suggests, compares a value within a column to either a specific value in a query, or the value of another column.

Some of the most common examples of Comparison Operators include:

Comparison Operator Examples

Let’s now look at an example of a Comparison Operator.

If we just had one specific publisher we wanted to see figures for the = operator comes in handy here. We simply were to write Publisher = ‘PublisherName’ and the result will filter to rows where this statement is true:

SELECT Name, Publisher
FROM vgsales
WHERE Publisher = 'Activision'
LIMIT 10;

Result

Arithmetic Operators

Arithmetic Operators are a bit different to Logical and Comparison Operators as they perform mathematical functions such as addition and subtraction.

The most commonly used Arithmetic Operators are:

Arithmetic Operator Examples

Let’s look at an example of arithmetic operators in action.

In our dataset we have global sales figures, along with some country specific numbers. What if we wanted to see what global sales excluding the USA? In this case we can create a new column output using the subtraction operator as below:

SELECT Name, Platform, Global_Sales - NA_Sales AS Global_Excluding_NA
FROM vgsales
LIMIT 10;

Result

This sums up the most commonly used Logical, Comparison and Arithmetic SQL Operators. There are other operators that exist in SQL, but this should cover the majority that you will use day to day, especially when learning the basics.

For more tutorials in the SQL Made Easy series check out the below:

• SQL SELECT DISTINCT
• SQL Primary Key
• SQL Wildcard Characters
• SQL Aggregate Functions