September 24, 2022

# 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.

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! ``````

``` ```
``` About the author Daniel Clark Daniel Clark is a data visualization journalist at the Times of London. In his free time, he enjoys running and watching football. More learning resources 15 Recruiters Reveal If SQL Certifications Are Worth It Read more How to Learn SQL Online Read more ```
``` Learn data skills 10x faster Join 1M+ learners Enroll for free Data Analyst (Python)Gen AI (Python)SQLExcelBusiness Analyst (Power BI)Business Analyst (Tableau)Machine LearningData Analyst (R) ```
``` ```
``` ```
``` ```
``` All rights reserved. Dataquest Labs, Inc. Terms of Use Privacy Policy About For Business For Educators About Dataquest Learner Stories Contact Us Partnership Programs Sitemap Career Paths Data Scientist – Python Data Analyst – Python Data Engineer Data Analyst – R Business Analyst – Power BI Business Analyst – Tableau Junior Data Analyst Skill Paths SQL Courses AI Courses Machine Learning Courses Deep Learning Courses Excel Courses Statistics Courses Explore Course Catalog Projects Data Roles Teaching Method Project-first Learning Sign In Start Free Dashboard Learning Path Catalog Full Catalog Career Paths Skill Paths Individual Courses Data Science Projects Resources A Better Way to Learn Understanding Data Roles Success Stories Blog Tutorials & Project walkthroughs For Teams ```