SQL Operators: 6 Different Types (w/ 45 Code Examples)
This article will cover what SQL operators are, the various types of operators, and many different code examples of how they're used.
As with any new skill, people prefer to learn in different ways. If learning by reading blog articles isn't your cup of tea, you may enjor our interactive SQL courses. Try them for free here.
What are SQL operators?
A 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 operator in SQL like the different buttons on a calculator function.
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;The
SELECT 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) = “data”
SET @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);
The
DECLARE @addValue int = 10
SET @addValue
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
SELECT *
FROM users
WHERE first_name LIKE �
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’.
SELECT ‘data’ + ‘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 = ‘data’
SET @strVar += ‘quest’
PRINT @strVar;
The
SELECT *
FROM users
WHERE first_name LIKE ‘da
[] (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 LIKE ‘d[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 LIKE ‘d[^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_
More helpful SQL resources:
- SQL Commands Reference List
- SQL Cheat Sheet (Downloadable PDF)
- SQL Interview Questions to Practice With
- Do You Need a SQL Certification?
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!
SELECT 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) = “data”
SET @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);
The
DECLARE @addValue int = 10
SET @addValue
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
SELECT *
FROM users
WHERE first_name LIKE �
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’.
SELECT ‘data’ + ‘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 = ‘data’
SET @strVar += ‘quest’
PRINT @strVar;
The
SELECT *
FROM users
WHERE first_name LIKE ‘da
[] (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 LIKE ‘d[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 LIKE ‘d[^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_
More helpful SQL resources:
- SQL Commands Reference List
- SQL Cheat Sheet (Downloadable PDF)
- SQL Interview Questions to Practice With
- Do You Need a SQL Certification?
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!
