Published: March 16, 2021

sql operators reference list

We have previously covered why you need to learn SQL to get a data job in 2021, as well as publishing a full list of SQL commands to help you get started. Next, we’re going to be looking at SQL operators.

We’re going to cover what exactly SQL operators are, before providing a comprehensive list of the different types with full examples for each.

If you're trying to learn SQL and reading these types of articles makes you want to bang your head against the wall, you're not alone. 

As with any new skill, people prefer to learn in different ways. That's why we created our interactive SQL courses. Regardless of where you are in your SQL journey, we've got a course for you.

Learn SQL the right way!

  • Writing real queries
  • In your browser
  • On your schedule

Why watch video lectures when  you can learn by doing?

What are SQL operators?

An SQL operator is a special word or character used to perform tasks. These tasks can be anything from complex comparisons, to basic arithmetic operations. Think of an SQL operator as similar to how the different buttons on a calculator function.

SQL operators are primarily used within the WHERE clause of an SQL statement. This is the part of the statement that is used to filter data by a specific condition or conditions.

There are six types of SQL operators that we are going to cover: Arithmetic, Bitwise, Comparison, Compound, Logical and String.

Arithmetic operators

Arithmetic operators are used for mathematical operations on numerical data, such as adding or subtracting.

+ (Addition)

The + symbol adds two numbers together.

SELECT 10 + 10;

- (Subtraction)

The - symbol subtracts one number from another.
SELECT 10 - 10;

* (Multiplication)

The * symbol multiples two numbers together.
SELECT 10 * 10;

/ (Division)

The / symbol divides one number by another.
SELECT 10 / 10;

% (Remainder/Modulus)

The % symbol (sometimes referred to as Modulus) returns the remainder of one number divided by another.
SELECT 10 % 10;

Bitwise operators

A bitwise operator performs bit manipulation between two expressions of the integer data type. Bitwise operators convert the integers into binary bits and then perform the AND (& symbol), OR (|, ^) or NOT (~) operation on each individual bit, before finally converting the binary result back into an integer.

Just a quick reminder: a binary number in computing is a number made up of 0s and 1s.

& (Bitwise AND)

The & symbol (Bitwise AND) compares each individual bit in a value with its corresponding bit in the other value. In the following example, we are using just single bits. Because the value of @BitOne is different to @BitTwo, a 0 is returned.
DECLARE @BitOne BIT = 1
DECLARE @BitTwo BIT = 0
SELECT @BitOne & @BitTwo;

But what if we make the value of both the same? In this instance, it would return a 1.

DECLARE @BitOne BIT = 1
DECLARE @BitTwo BIT = 1
SELECT @BitOne & @BitTwo;

Obviously this is just for variables that are type BIT. What would happen if we started using numbers instead? Take the example below:

DECLARE @BitOne INT = 230
DECLARE @BitTwo INT = 210
SELECT @BitOne & @BitTwo;

The answer returned here would be 194.

You might be thinking, “How on earth is it 194?!” and that’s perfectly understandable. To explain why, we first need to convert the two numbers into their binary form:

@BitOne (230) - 11100110
@BitTwo (210) - 11010010

Now, we have to go through each bit and compare (so the 1st bit in @BitOne and the 1st bit in @BitTwo). If both numbers are 1, we record a 1. If one or both are 0, then we record a 0:

@BitOne (230) - 11100110
@BitTwo (210) - 11010010
Result        - 11000000

The binary we are left with is 11000000, which if you google is equal to a numeric value of 194.

Confused yet? Don’t worry! Bitwise operators can be confusing to understand, but they’re rarely used in practice.

&= (Bitwise AND Assignment)

The &= symbol (Bitwise AND Assignment) does the same as the Bitwise AND (&) operator but then sets the value of a variable to the result that is returned.

| (Bitwise OR)

The | symbol (Bitwise OR) performs a bitwise logical OR operation between two values. Let’s revisit our example from before:

DECLARE @BitOne INT = 230
DECLARE @BitTwo INT = 210
SELECT @BitOne | @BitTwo;

In this instance, we have to go through each bit again and compare, but this time if EITHER number is a 1, then we record a 1. If both are 0, then we record a 0:

@BitOne (230) - 11100110
@BitTwo (210) - 11010010
Result        - 11110110

The binary we are left with is 11110110, which equals a numeric value of 246.

|= (Bitwise OR Assignment)

The |= symbol (Bitwise OR Assignment) does the same as the Bitwise OR (|) operator but then sets the value of a variable to the result that is returned.

^ (Bitwise exclusive OR)

The ^ symbol (Bitwise exclusive OR) performs a bitwise logical OR operation between two values.
DECLARE @BitOne INT = 230
DECLARE @BitTwo INT = 210
SELECT @BitOne ^ @BitTwo;

In this example, we compare each bit and return 1 if one, but NOT both bits are equal to 1.

@BitOne (230) - 11100110
@BitTwo (210) - 11010010
Result        - 00110100

The binary we are left with is 00110100, which equals a numeric value of 34.

^= (Bitwise exclusive OR Assignment)

The ^= symbol (Bitwise exclusive OR Assignment) does the same as the Bitwise exclusive OR (^) operator but then sets the value of a variable to the result that is returned.

Comparison operators

A comparison operator is used to compare two values and test whether they are the same.

= (Equal to)

The = symbol is used to filter results that equal a certain value. In the below example, this query will return all customers that have an age of 20.

SELECT * FROM customers
WHERE age = 20;

!= (Not equal to)

The != symbol is used to filter results that do not equal a certain value. In the below example, this query will return all customers that don't have an age of 20.
SELECT * FROM customers
WHERE age != 20;

> (Greater than)

The > symbol is used to filter results where a column’s value is greater than the queried value. In the below example, this query will return all customers that have an age above 20.

SELECT * FROM customers
WHERE age > 20;

!> (Not greater than)

The !> symbol is used to filter results where a column’s value is not greater than the queried value. In the below example, this query will return all customers that do not have an age above 20.

SELECT * FROM customers
WHERE age !> 20;

< (Less than)

The < symbol is used to filter results where a column’s value is less than the queried value. In the below example, this query will return all customers that have an age below 20.

SELECT * FROM customers
WHERE age < 20;

!< (Not less than)

The !< symbol is used to filter results where a column’s value is not less than the queried value. In the below example, this query will return all customers that do not have an age below 20.
SELECT * FROM customers
WHERE age !< 20;

>= (Greater than or equal to)

The >= symbol is used to filter results where a column’s value is greater than or equal to the queried value. In the below example, this query will return all customers that have an age equal to or above 20.

SELECT * FROM customers
WHERE age >= 20;

<= (Less than or equal to)

The <= symbol is used to filter results where a column’s value is less than or equal to the queried value. In the below example, this query will return all customers that have an age equal to or below 20.
SELECT * FROM customers
WHERE age <= 20;

<> (Not equal to)

The <> symbol performs the exact same operation as the != symbol and is used to filter results that do not equal a certain value. You can use either, but <> is the SQL-92 standard.
SELECT * FROM customers
WHERE age <> 20;

Compound operators

Compound operators perform an operation on a variable and then set the result of the variable to the result of the operation. Think of it as doing a = a (+,-,*,etc) b.

+= (Add equals)

The += operator will add a value to the original value and store the result in the original value. The below example sets a value of 10, then adds 5 to the value and prints the result (15).

DECLARE @addValue int = 10
SET @addValue += 5
PRINT CAST(@addvalue AS VARCHAR);

This can also be used on strings. The below example will concatenate two strings together and print “dataquest”.

DECLARE @addString VARCHAR(50) =dataSET @addString += “quest”
PRINT @addString;

-= (Subtract equals)

The -= operator will subtract a value from the original value and store the result in the original value. The below example sets a value of 10, then subtracts 5 from the value and prints the result (5).

DECLARE @addValue int = 10
SET @addValue -= 5
PRINT CAST(@addvalue AS VARCHAR);

*= (Multiply equals)

The *= operator will multiple a value by the original value and store the result in the original value. The below example sets a value of 10, then multiplies it by 5 and prints the result (50).
DECLARE @addValue int = 10
SET @addValue *= 5
PRINT CAST(@addvalue AS VARCHAR);

/= (Divide equals)

The /= operator will divide a value by the original value and store the result in the original value. The below example sets a value of 10, then divides it by 5 and prints the result (2).
DECLARE @addValue int = 10
SET @addValue /= 5
PRINT CAST(@addvalue AS VARCHAR);

%= (Modulo equals)

The %= operator will divide a value by the original value and store the remainder in the original value. The below example sets a value of 25, then divides by 5 and prints the result (0).
DECLARE @addValue int = 10
SET @addValue %= 5
PRINT CAST(@addvalue AS VARCHAR);

Logical operators

Logical operators are those that return true or false, such as the AND operator, which returns true when both expressions are met.

ALL

The ALL operator returns TRUE if all of the subquery values meet the specified condition. In the below example, we are filtering all users who have an age that is greater than the highest age of users in London.
SELECT first_name, last_name, age, location
FROM users
WHERE age > ALL (SELECT age FROM users WHERE location = ‘London’);

ANY/SOME

The ANY operator returns TRUE if any of the subquery values meet the specified condition. In the below example, we are filtering all products which have any record in the orders table. The SOME operator achieves the same result.
SELECT product_name
FROM products
WHERE product_id > ANY (SELECT product_id FROM orders);

AND

The AND operator returns TRUE if all of the conditions separated by AND are true. In the below example, we are filtering users that have an age of 20 and a location of London.

SELECT *
FROM users
WHERE age = 20 AND location = ‘London’;

BETWEEN

The BETWEEN operator filters your query to only return results that fit a specified range.

SELECT *
FROM users
WHERE age BETWEEN 20 AND 30;

EXISTS

The EXISTS operator is used to filter data by looking for the presence of any record in a subquery.

SELECT name
FROM customers
WHERE EXISTS
(SELECT order FROM ORDERS WHERE customer_id = 1);

IN

The IN operator includes multiple values set into the WHERE clause.

SELECT *
FROM users
WHERE first_name IN (‘Bob’, ‘Fred’, ‘Harry’);

LIKE

The LIKE operator searches for a specified pattern in a column. (For more information on how/why the % is used here, see the section on the wildcard character operator).

SELECT *
FROM users
WHERE first_name LIKE%Bob%;

NOT

The NOT operator returns results if the condition or conditions are not true.
SELECT *
FROM users
WHERE first_name NOT IN (‘Bob’, ‘Fred’, ‘Harry’);

OR 

The OR operator returns TRUE if any of the conditions separated by OR are true.In the below example, we are filtering users that have an age of 20 or a location of London.

SELECT *
FROM users
WHERE age = 20 OR location = ‘London’;

IS NULL

The IS NULL operator is used to filter results with a value of NULL.
SELECT *
FROM users
WHERE age IS NULL;

String operators

String operators are primarily used for string concatenation (combining two or more strings together) and string pattern matching.

+ (String concatenation)

The + operator can be used to combine two or more strings together. The below example would output ‘dataquest’.
SELECTdata+ ‘quest’;

+= (String concatenation assignment)

The += is used to combine two or more strings and store the result in the original variable. The below example sets a variable of ‘data’, then adds ‘quest’ to it, giving the original variable a value of ‘dataquest’.

DECLARE @strVar VARCHAR(50)
SET @strVar =dataSET @strVar += ‘quest’
PRINT @strVar;

% (Wildcard)

The % symbol - sometimes referred to as the wildcard character - is used to match any string of zero or more characters. The wildcard can be used as either a prefix or a suffix. In the below example, the query would return any user with a first name that starts with ‘dan’.

SELECT *
FROM users
WHERE first_name LIKE ‘dan%;

[] (Character(s) matches)

The [] is used to match any character within the specific range or set that is specified between the square brackets. In the below example, we are searching for any users that have a first name that begins with a d and a second character that is somewhere in the range c to r.

SELECT *
FROM users
WHERE first_name LIKEd[c-r]%’’;

[^] (Character(s) not to match)

The [^] is used to match any character that is not within the specific range or set that is specified between the square brackets. In the below example, we are searching for any users that have a first name that begins with a d and a second character that is not a.

SELECT *
FROM users
WHERE first_name LIKEd[^a]%’’;

_ (Wildcard match one character)

The _ symbol - sometimes referred to as the underscore character - is used to match any single character in a string comparison operation. In the below example, we are searching for any users that have a first that begins with a d and has a third character that is n. The second character can be any letter.

SELECT *
FROM users
WHERE first_name LIKE ‘d_n%;

More helpful SQL resources:

Or, try the best SQL learning resource of all: interactive SQL courses you can take right in your browser. Sign up for a FREE account and start learning!

Learn SQL the right way!

  • Writing real queries
  • In your browser
  • On your schedule

Why watch video lectures when  you can learn by doing?


Tags


SQL, sql operators


You may also like

How to Maximize Your Data Science Salary
Top Conferences for Data Professionals