What are SQL operators?

SQL operators are at the heart of data queries and data management in SQL. They include comparison, logical, and arithmetic operators. You can combine operators with each other to carry out both simple and complex queries.

What are SQL operators used for?

SQL operators consist of one or more symbols or characters that execute an action or operation. Some of the most important uses of operators are:

  • Comparing data
  • Filtering and sorting data
  • Arithmetic calculations
  • Logical operations
  • Comparing patterns
  • Analysing datasets
  • Checking NULL values

What are the different types of SQL operators?

SQL operators can be sorted into different categories based on which kind of action and data query they can help with. There are:

  • Comparison operators: They make comparisons between the selected values. They can be used to define criteria for filtering, sorting or grouping data.
  • Logical operators: These operators test the truth value of conditions by forming Boolean expressions that return TRUE, FALSE OR UNKNOWN. They include AND, OR and NOT.
  • Arithmetic operators: These operators are used to perform mathematical calculations with numerical data and values. They can, among other things, add, subtract and divide values in selected columns. Other operations include multiplication, percentage calculation and root calculation.
  • String operators: These operators work with character strings. They can be used to search columns for exact terms, patterns or substrings, or to make comparisons. String operators include LIKE and SQL wildcards like the percent sign.
  • Set operators: Set operators are used to perform operations with datasets and return a result set based on the conditions you’ve defined. They can manipulate datasets, create intersections and differences or create union sets. Examples include UNION and EXCEPT.

Depending on which database and database management system you use, you might have access to additional SQL operators not listed here.

Tip

Dive into the world of SQL and learn the most important basics with our SQL introduction with examples.

Overview of the most important SQL operators

Below we’ll introduce some of the most important SQL operators, categorised by operator type.

Comparison operators

SQL operator Function
= Checks whether two values are the same and returns either TRUE or FALSE; filters, updates or creates values that satisfy the comparison
<> and != Checks whether two values are different and returns either TRUE or FALSE; filters or compares column values or tables
< Checks whether one value is smaller than another
> Checks whether one value is larger than another
<= and >= Checks whether a value is less than or equal to/greater than or equal to another
!< and !> Checks whether values are not less than or equal to/greater than or equal to each other

Logical operators

SQL operator Function
AND Both values need to hold
OR At least one value needs to hold
NOT Checks datasets for values that do not fulfill a condition or that satisfy a negated condition
ALL Checks whether all the selected values satisfy a certain condition
BETWEEN Checks whether values lie within a range
EXISTS Checks whether a value is present in a dataset
IN Checks whether a value is present in a list of elements or values
LIKE Checks whether a value matches an exact string or pattern
SOME Checks whether at least one value in a list or column satisfies a condition

Arithmetic operators

SQL operator Function
+ Addition
- Subtraction
* Multiplication
/ Division
% Modulo

String operators

SQL operator Function
LIKE Checks whether records match a search pattern defined by the user
NOT LIKE Excludes records that don’t match a search pattern
% Serves as a placeholder (wildcard) for one or more unknown characters; can be used for substrings where the first and last character are unknown
[…] Defines a character set within the square brackets, for example a letter between A and F as [a-f]
_ Replaces a single character in a search pattern

Set operators

SQL operator Function
UNION Unites two sets of unique rows and omits duplicate elements
INTERSECT Retrieves the records that two or more SELECT clauses have in common and only returns the records that are present in both
EXCEPT Retrieves the records that exist in the first query or the left table but not in in the second query or right table

Other operators

SQL operator Function
NVL, ISNULL, IFNULL Checks for NULL values and replaces them with a user-defined value
DISTINCT Used as a processing or comparison operator with SELECT to remove duplicate records
Tip

The success of your web project and database management depends on having a scalable, high-performing and secure SQL solution. SQL Server Hosting from IONOS provides hosting and service offers for your individual needs.

VPS Hosting
Fully virtualised servers with root access
  • Unlimited traffic and up to 1 Gbit/s bandwidth
  • Fast SSD NVMe storage
  • Free Plesk Web Host Edition

What’s the difference between operators, functions and clauses in SQL?

It can be easy to get SQL operators, functions and clauses confused. Here are some of the most important differences between them:

  • SQL operators: Usually consist of one or more symbols or characters that are responsible for an operation. They combine values, strings and columns to carry out operations like comparisons and arithmetic calculations, and then return a value. They are processed before functions and clauses.
  • SQL functions: Used for complex actions, operations and calculations that consist of several arguments, parameters and conditions.
  • SQL clauses: Define conditions and criteria for operations with operators or functions. They mostly consist of key words like SELECT, WHERE, ORDER BY and GROUP BY.

Note that there are overlaps between some operators and functions in their application.

Was this article helpful?