SQL Operators Explained

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:

OperatorDescription
ALLChecks a value matches against all values that results from a subquery
ANDSpecifies that a value must meet multiple conditions
ANYChecks a value matches against any value that results from a subquery
BETWEENChecks against a range of values that are specified
EXISTSChecks if a specified value exists within the data
INChecks against a specific list of values entered
LIKESearches for a specific pattern or list of characters in a value
NOTUsed with other operators to exclude rather than include values
ORSpecified that a value must meet at least one of the conditions
SOMEChecks a value matches against some of the values that results from a subquery

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:

NameGenre
Wii SportsSports
Mario Kart WiiRacing
Wii Sports ResortSports
Mario Kart DSRacing
Wii FitSports
Wii Fit PlusSports
Gran Turismo 3: A-SpecRacing
Mario Kart 7Racing
Gran Turismo 4Racing
Gran TurismoRacing

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

NameGlobal_Sales
Super Mario Bros.40.24
Mario Kart Wii35.82
New Super Mario Bros.30.01
New Super Mario Bros. Wii28.62
Mario Kart DS23.42
Super Mario World20.61
Super Mario Land18.14
Super Mario Bros. 317.28
Mario Kart 712.21
Super Mario 6411.89

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:

DescriptionDescription
=Equal To
>Greater Than
<Less Than
>=Greater Than or Equal To
<=Less Than or Equal To
!= or <>Not Equal To

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

NamePublisher
Call of Duty: Modern Warfare 3Activision
Call of Duty: Black OpsActivision
Call of Duty: Black Ops 3Activision
Call of Duty: Black Ops IIActivision
Call of Duty: Black Ops IIActivision
Call of Duty: Modern Warfare 2Activision
Call of Duty: Modern Warfare 3Activision
Call of Duty: Black OpsActivision
Call of Duty: Modern Warfare 2Activision
Call of Duty: GhostsActivision

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:

OperatorDescription
+Addition
Subtraction
*Multiplication
/Division
%Modulo

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

NamePlatformGlobal_Excluding_NA
Wii SportsWii41.25
Super Mario Bros.NES11.16
Mario Kart WiiWii19.97
Wii Sports ResortWii17.25
Pokemon Red/Pokemon BlueGB20.1
TetrisGB7.06
New Super Mario Bros.DS18.63
Wii PlayWii14.99
New Super Mario Bros. WiiWii14.03
Duck HuntNES1.38

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: