SQL Operators: 6 Different Types (w/ Examples)
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…
Category: sql operators
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…
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.
Why watch video lectures when you can learn by doing?
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 are used for mathematical operations on numerical data, such as adding or subtracting.
The + symbol adds two numbers together.
SELECT 10 + 10;SELECT 10 - 10;SELECT 10 * 10;SELECT 10 / 10;SELECT 10 % 10;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.
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.
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.
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.
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.
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.
A comparison operator is used to compare two values and test whether they are the same.
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;SELECT * FROM customers
WHERE age != 20;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;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;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;SELECT * FROM customers
WHERE age !< 20;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;SELECT * FROM customers
WHERE age <= 20;SELECT * FROM customers
WHERE age <> 20;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.
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;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);DECLARE @addValue int = 10
SET @addValue *= 5
PRINT CAST(@addvalue AS VARCHAR);DECLARE @addValue int = 10
SET @addValue /= 5
PRINT CAST(@addvalue AS VARCHAR);DECLARE @addValue int = 10
SET @addValue %= 5
PRINT CAST(@addvalue AS VARCHAR);Logical operators are those that return true or false, such as the AND operator, which returns true when both expressions are met.
SELECT first_name, last_name, age, location
FROM users
WHERE age > ALL (SELECT age FROM users WHERE location = ‘London’);SELECT product_name
FROM products
WHERE product_id > ANY (SELECT product_id FROM orders);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’;The BETWEEN operator filters your query to only return results that fit a specified range.
SELECT *
FROM users
WHERE age BETWEEN 20 AND 30;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);The IN operator includes multiple values set into the WHERE clause.
SELECT *
FROM users
WHERE first_name IN (‘Bob’, ‘Fred’, ‘Harry’);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%’;SELECT *
FROM users
WHERE first_name NOT IN (‘Bob’, ‘Fred’, ‘Harry’);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’;SELECT *
FROM users
WHERE age IS NULL;String operators are primarily used for string concatenation (combining two or more strings together) and string pattern matching.
SELECT ‘data’ + ‘quest’;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 % 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%’;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]%’’;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]%’’;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%’;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!
Why watch video lectures when you can learn by doing?
There was a problem reporting this post.
Please confirm you want to block this member.
You will no longer be able to:
Please note: This action will also remove this member from your connections and send a report to the site admin. Please allow a few minutes for this process to complete.