SQL Operators: 6 Different Types & Code Examples

SQL is a powerful tool used to interact with relational databases. One of its key features is the use of SQL operators, which enable users to perform various operations on data within the database. Understanding these operators is essential for writing efficient and effective SQL queries. In this article, we will delve into the six different types of SQL operators, providing comprehensive explanations and real-world examples to help users grasp their functionalities and leverage them to their full potential.

Quick summary:

SQL operators are symbols and keywords used in SQL queries to calculate values, compare data, filter rows, and apply logic—used by anyone writing SELECT queries in relational databases.

Key takeaway:

Understanding operator types and precedence ensures your queries return the correct results—especially when combining multiple conditions.

Quick promise:

By the end, you’ll know how to use arithmetic, comparison, logical, LIKE, and BETWEEN operators with confidence and avoid common query errors.

Quick Facts — SQL Operators

Summary:

• Used inside SQL queries to manipulate and filter data
• Work with numbers, text, and dates
• Return calculated values or Boolean results (TRUE/FALSE)
• Follow a defined order of precedence
• Essential for SELECT, WHERE, and filtering logic

Arithmetic Operators

Arithmetic operators are fundamental components of SQL that allow users to perform basic mathematical calculations on numeric data within relational databases. These operators are essential for performing various calculations such as addition, subtraction, multiplication, division, and finding remainders. 

• Addition (+)

The addition operator (+) is used to add two numeric values together. It is one of the most basic arithmetic operators and is widely used in SQL calculations.

Usage Example:

Suppose we have a table “sales” with columns “quantity_sold” and “price_per_unit.” To calculate the total revenue for each sale, we can use the addition operator as follows:

SELECT quantity_sold, price_per_unit, quantity_sold + price_per_unit AS total_revenue
FROM sales;

• Subtraction (-)

The subtraction operator (-) is used to subtract one numeric value from another. It allows us to find the difference between two numbers.

Usage Example:

Suppose we have a table “expenses” with columns “total_income” and “total_expenditure.” To calculate the net income, we can use the subtraction operator as follows:

SELECT total_income, total_expenditure, total_income - total_expenditure AS net_income
FROM expenses;

• Multiplication (*)

The multiplication operator (*) is used to multiply two numeric values together. It is useful for calculating totals or finding percentages.

Usage Example:

Suppose we have a table “products” with columns “quantity_sold” and “unit_price.” To calculate the total revenue for each product, we can use the multiplication operator as follows:

SELECT product_name, quantity_sold, unit_price, quantity_sold * unit_price AS total_revenue
FROM products;

• Division (/)

The division operator (/) is used to divide one numeric value by another. It is employed to find ratios or calculate averages.

Usage Example:

Suppose we have a table “students” with columns “total_marks” and “subjects_attempted.” To calculate the average marks obtained by each student, we can use the division operator as follows:

SELECT student_name, total_marks, subjects_attempted, total_marks / subjects_attempted AS average_marks
FROM students;

• Modulo (%)

The modulo operator (%) calculates the remainder of a division between two numeric values. It is particularly useful for tasks that involve cyclic operations or repeating patterns.

Usage Example:

Suppose we have a table “employees” with a column “employee_id.” To find the number of employees in each department, we can use the modulo operator as follows:

SELECT department_name, COUNT(employee_id) % 5 AS employees_in_department
FROM employees
GROUP BY department_name; 

Operator Precedence Rules

Operator precedence refers to the order in which arithmetic operators are evaluated within an SQL expression. It is crucial to understand these rules to ensure the correct calculation of results.

Operator Precedence Hierarchy (Highest to Lowest):

• Parentheses ()
• Exponentiation (^)
• Multiplication (*), Division (/), and Modulo (%)
• Addition (+) and Subtraction (-)

Comparison Operators

Comparison operators are fundamental elements in SQL that allow users to compare values and expressions to determine their relationship. These operators are vital for filtering and sorting data, as they return a Boolean result (True or False) based on the comparison.

• Equal to (=)

The equal to the operator (=) checks if two values or expressions are equal. It is commonly used for exact matches in comparisons.

Usage Example:

Suppose we have a table “employees” with a column “department” and want to retrieve all employees in the “Sales” department:

SELECT *
FROM employees
WHERE department = 'Sales';

• Not equal to (<> or !=)

The not equal to the operator (<> or !=) checks if two values or expressions are not equal. It is used to exclude specific values from the result set.

Usage Example:

Suppose we have a table “products” with a column “category” and want to retrieve all products that do not belong to the “Electronics” category:

SELECT *
FROM products
WHERE category <> 'Electronics';

• Greater than (>)

The greater than operator (>) checks if the left operand is greater than the right operand. It is useful for finding records with values higher than a given threshold.

Usage Example:

Suppose we have a table “orders” with a column “order_total” and want to retrieve all orders with a total value greater than $1000:

SELECT *
FROM orders
WHERE order_total > 1000;

• Less than (<)

The less than operator (<) checks if the left operand is less than the right operand. It is employed to find records with values lower than a specific limit.

Usage Example:

Suppose we have a table “students” with a column “marks_obtained” and want to retrieve all students with marks less than 60:

`SELECT *
FROM students
WHERE marks_obtained < 60;

• Greater than or equal to (>=)

The greater than or equal to the operator (>=) checks if the left operand is greater than or equal to the right operand. It includes records with values equal to the specified threshold.

Usage Example:

Suppose we have a table “employees” with a column “years_of_service” and want to retrieve all employees with at least five years of service:

SELECT *
FROM employees
WHERE years_of_service >= 5;

• Less than or equal to (<=)

The less than or equal to operator (<=) checks if the left operand is less than or equal to the right operand. It includes records with values equal to the specified limit.

Usage Example: Suppose we have a table “products” with a column “price” and want to retrieve all products with a price less than or equal to $50:

SELECT *
FROM products
WHERE price <= 50;

• Handling NULL Values

Dealing with NULL values requires special attention in comparisons. The regular comparison operators may not work as expected when compared with NULL. SQL provides special operators like IS NULL and IS NOT NULL to handle NULL values in queries.

Usage Example:

Suppose we have a table “students” with a column “date_of_birth” and want to retrieve all students with missing birth dates:

SELECT *

FROM students

WHERE date_of_birth IS NULL;

Logical Operators

Logical operators in SQL are used to combine multiple conditions in a query and evaluate them as a single Boolean result (True or False). These operators are fundamental for implementing complex conditions, making decisions, and filtering data based on multiple criteria. 

• AND Operator

The AND operator returns true only if all the conditions connected by AND are true. It is used to narrow down the result set by requiring that multiple conditions must be satisfied simultaneously.

Usage Example:

Suppose we have a table “employees” with columns “department” and “years_of_service” and want to retrieve all employees who are in the “Sales” department and have more than five years of service:

SELECT *
FROM employees
WHERE department = 'Sales' AND years_of_service > 5;

• OR Operator

The OR operator returns true if at least one of the conditions connected by OR is true. It is used to broaden the result set by including records that meet any of the specified conditions.

Usage Example:

Suppose we have a table “products” with columns “category” and “stock_quantity” and want to retrieve all products that are in the “Electronics” category or have a stock quantity greater than 100:

SELECT *
FROM products
WHERE category = 'Electronics' OR stock_quantity > 100;

• NOT Operator

The NOT operator negates the result of a single condition. It returns true if the condition is false and false if the condition is true. It is used to reverse the outcome of a particular condition.

Usage Example:

Suppose we have a table “students” with a column “is_graduated” and want to retrieve all students who have not yet graduated:

SELECT *
FROM students
WHERE NOT is_graduated;

• Precedence in Complex Queries

In complex queries involving multiple logical operators, it’s essential to understand the order of evaluation. SQL follows a specific precedence hierarchy when combining AND, OR, and NOT operators.

Logical Operator Precedence Hierarchy:

1. NOT
2. AND
3. OR

Usage Example:

Suppose we have a table “employees” with columns “department,” “salary,” and “years_of_service” and want to retrieve all employees who are either in the “Sales” department and have a salary greater than 50000 or have more than five years of service:

SELECT *
FROM employees
WHERE department = 'Sales' AND salary > 50000 OR years_of_service > 5;

LIKE Operator

LIKE operator is particularly useful when dealing with text data that follows a particular format or contains specific characters. In this detailed overview, we will explore the syntax, usage, and wildcard characters of the LIKE operator, along with providing real-world examples to illustrate its practical application in SQL queries.

• Syntax and usage

The syntax of the LIKE operator in SQL is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;

The column_name represents the column you want to search in, and pattern is the string pattern you want to match against. The LIKE operator searches for matches in the specified column based on the pattern provided.

Usage Example:

Suppose we have a table “employees” with a column “first_name,” and we want to retrieve all employees whose first name starts with the letter “J”:

SELECT *
FROM employees
WHERE first_name LIKE 'J%';

• Wildcard characters (% and _)

The LIKE operator supports two wildcard characters:

1. Percent Sign (%): It represents zero, one, or multiple characters. When used at the beginning of a pattern, it matches any sequence of characters. When used at the end, it matches any characters followed by the specified sequence.
2. Underscore (_): It represents a single character. When used in a pattern, it matches any single character in that position.

Usage Example:

Suppose we have a table “products” with a column “product_name,” and we want to retrieve all products whose names start with “Super” and have any character after it:

SELECT *
FROM products
WHERE product_name LIKE 'Super%';

Examples of pattern matching

The LIKE operator can be used to perform various types of pattern matching, such as finding values with a specific prefix, or suffix, or containing specific characters.

Usage Examples:

1. Retrieving all employees whose last name ends with “son”:

SELECT *
FROM employees
WHERE last_name LIKE '%son';

2. Retrieving all products whose names contain “laptop” anywhere in the string:

SELECT *
FROM products
WHERE product_name LIKE '%laptop%';

IN Operator

IN Operator allows users to specify a list of values, and the operator checks if a given expression matches any of the values in the list. The IN operator is particularly useful when dealing with complex queries that involve multiple OR conditions.

• Syntax and Usage

The syntax of the IN operator in SQL is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE column_name IN (value1, value2, ...);

The column_name represents the column you want to compare with the list of values, and (value1, value2, …) is the list of values you want to check against. The IN operator will return true if the column_name matches any of the specified values in the list.

Usage Example:

Suppose we have a table “employees” with a column “department,” and we want to retrieve all employees who belong to either the “Sales” or “Marketing” department:

SELECT *
FROM employees
WHERE department IN ('Sales', 'Marketing');

Simplifying Queries with Multiple Values

The IN operator simplifies queries that involve multiple values for comparison. Instead of using multiple OR conditions, the IN operator allows users to specify all the values in a single list, making the query more concise and easier to read.
Usage Example:
Suppose we have a table “orders” with a column “status,” and we want to retrieve all orders with either “Shipped,” “Delivered,” or “Out for Delivery” status:

SELECT *
FROM orders
WHERE status IN ('Shipped', 'Delivered', 'Out for Delivery');

The IN operator can offer performance benefits over multiple OR conditions, especially when dealing with large datasets. The database optimizer can efficiently process the IN operator, resulting in faster query execution.

BETWEEN Operator

BETWEEN Operator allows users to specify a range of values, and the operator checks if a given expression falls within that range. The BETWEEN operator is particularly useful when dealing with date ranges, numeric intervals, or filtering data based on specific conditions.

• Syntax and Usage

The syntax of the BETWEEN operator in SQL is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

The column_name represents the column you want to compare with the range, and value1 and value2 are the boundaries of the range. The BETWEEN operator will return true if the column_name falls within the specified range, inclusive of the boundaries.

Usage Example:

Suppose we have a table “products” with a column “price,” and we want to retrieve all products with a price between $50 and $100:

SELECT *
FROM products
WHERE price BETWEEN 50 AND 100;

• Filtering Data within Specific Intervals

The main essence of the BETWEEN operator is to filter data within specific intervals or ranges. It is often used in conjunction with numeric or date columns to extract relevant information based on conditions that involve specific ranges.

Usage Example:

Suppose we have a table “employees” with a column “years_of_service,” and we want to retrieve all employees who have been with the company for more than five years but less than or equal to ten years:

SELECT *
FROM employees
WHERE years_of_service BETWEEN 5 AND 10;

• Performance Benefits

The BETWEEN operator can offer performance benefits over using multiple comparison operators, especially when dealing with large datasets. The database optimizer can efficiently process the BETWEEN operator, resulting in faster query execution.

Usage Example:

Suppose we have a table “orders” with a column “order_date,” and we want to retrieve all orders placed between January 1, 2023, and June 30, 2023:

SELECT *
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-06-30';

Understanding the syntax, benefits, and usage of the BETWEEN operator empowers users to efficiently handle complex data filtering scenarios. As you continue your SQL journey, remember that the BETWEEN operator allows you to perform precise range-based queries, making it an essential component in data analysis and reporting tasks.

FAQs on SQL Operators

Can I use multiple logical operators in the same SQL query?

Yes, you can combine AND, OR, and NOT in a single query. SQL is designed to handle multiple logical conditions to filter data more precisely.

• Use AND to narrow results (all conditions must be true).
• Use OR to broaden results (at least one condition must be true).
• Use NOT to reverse a condition.

For complex logic, use parentheses to ensure the correct evaluation order.

What happens if I don’t use parentheses with multiple logical operators?

SQL will follow its default operator precedence rules, which may change your intended logic.

Logical operators are evaluated in this order:

1. NOT
2. AND
3. OR

If you mix AND and OR without parentheses, SQL may evaluate conditions differently than expected, leading to incorrect results.

Best practice: Always group mixed logical conditions explicitly.

How does operator precedence work in SQL?

Operator precedence defines the order in which SQL evaluates operators in an expression.

Arithmetic operators are evaluated from highest to lowest priority:

• Parentheses
• Exponentiation
• Multiplication, Division, Modulo
• Addition, Subtraction

Logical operators are evaluated in this order:

• NOT
• AND
• OR

If you need a different evaluation sequence, use parentheses to override the default order.

Does the BETWEEN operator include boundary values?

Yes, BETWEEN is inclusive. It includes both the lower and upper values specified.

If you filter values between 50 and 100, the result includes:

• 50
• 100
• All values in between

BETWEEN works with numeric and date values.

Can I use BETWEEN with date values

Yes, BETWEEN works with dates as well as numbers. It is commonly used to filter records within a specific date range.

For example, you can retrieve records between January 1 and June 30 of a given year. The result will include both boundary dates.

This makes BETWEEN useful for reporting and time-based analysis.

How do I handle NULL values in comparisons?

Standard comparison operators do not work correctly with NULL values. You must use special operators.

Use:

• IS NULL to find missing values
• IS NOT NULL to find non-missing values

Comparisons like equals (=) or not equals (<>) do not return expected results when NULL is involved.

Can I search for a character anywhere in a string using LIKE?

Yes, you can use the percent (%) wildcard to match text anywhere within a string.

• % matches zero, one, or multiple characters
• _ matches exactly one character

By placing % before and after a word, you can find matches regardless of position.

LIKE is commonly used for name searches and partial text matching.

Can I combine multiple wildcards in a single LIKE pattern?

Yes, multiple wildcards can be combined to create more specific search patterns.

You can:

• Use % at the beginning and end of a pattern
• Combine % and _ in the same condition
• Build complex matching rules

This is useful for advanced text filtering scenarios.

Can I use the IN operator with non-numeric data?

Yes, IN works with both numeric and text values. It checks whether a column matches any value from a specified list.

It is especially useful when:

• Filtering multiple categories
• Matching multiple string values
• Simplifying multiple OR conditions

IN makes queries more readable than repeating multiple comparisons.

Can I use subqueries with the IN operator?

Yes, IN can compare a column against results returned from a subquery.

This allows you to:

• Filter based on another query’s results
• Match values dynamically
• Build layered filtering logic

This is commonly used in reporting and relational filtering tasks.

What is the most common mistake when using SQL operators?

The most common mistake is ignoring operator precedence. This often happens when mixing AND and OR without parentheses.

Other frequent mistakes include:

• Comparing values directly to NULL
• Forgetting that BETWEEN is inclusive
• Misusing wildcards in LIKE
• Choosing the wrong arithmetic operator

Careful grouping and testing conditions separately can prevent most issues.

One-minute summary

• SQL operators perform calculations, comparisons, and logical filtering.
• Main types: Arithmetic, Comparison, Logical, LIKE, BETWEEN.
• Operator precedence affects results.
• BETWEEN is inclusive.
• Always handle NULL values explicitly.

Key terms

• Arithmetic operators: Perform numeric calculations (+, -, *, /, %).
• Comparison operators: Compare values and return TRUE or FALSE.
• Logical operators: Combine multiple conditions (AND, OR, NOT).
• LIKE operator: Matches text patterns using wildcards.
• BETWEEN operator: Filters values within an inclusive range.
• Wildcard (%): Matches zero or more characters.
• Wildcard (_): Matches exactly one character.
• NULL: Represents missing or unknown data.
• Operator precedence: The order SQL uses to evaluate operators.

Conclusion

As you’ve now seen the immense power and versatility of SQL operators, it’s time to take your data engineering skills to the next level. At the Data Engineering Academy, we are committed to providing you with an unparalleled learning experience that goes beyond just understanding SQL operators. Our comprehensive curriculum covers a wide range of essential data engineering topics, equipping you with the knowledge and expertise needed to excel in this dynamic field.

Chris Garzon

Christopher Garzon has worked as a data engineer for Amazon, Lyft, and an asset management start up where he was responsible for building the entire Data Infrastructure from scratch. He is the author “Ace the Data Engineer Interview” and has helped 100’s of students break into the data engineer industry. He is also an angel investor, an advisor to multiple to multiple start ups, and the founder and CEO of Data Engineer Academy.