This blog is a comprehensive guide on SQL operators where we will explore several operators available in SQL along with their usage. This blog will help beginners learn the basics of SQL operators and experienced SQL users can expand their knowledge as this blog provides valuable insights for SQL querying skills.
Introduction to SQL operators
SQL operators are an essential component of SQL language and allow the users to perform a large number of operations on data stored in the database. Operators help us manipulate, combine and compare the data for retrieval of information and help us work on complex queries and calculations.
SQL operators can be categorized into a lot of types based on their functionality. Some of the most common types of SQL operators are:
Arithmetic Operators
The arithmetic operators are used for mathematical calculations on numeric data types such as:
| Operator | Description | Example |
|---|---|---|
| Addition | Used for adding two numeric values | SELECT 5 + 4; Result: 9 |
| Subtraction | Used for subtracting one numeric value from another | SELECT 5 – 4; Result: 1 |
| Multiplication | Used for multiplying two numeric values | SELECT 5 * 4; Result: 20 |
| Division | Used for dividing one numeric value by another | SELECT 18 / 3; Result: 6 |
| Modulus | Used for getting the remainder of a division operation | SELECT 5 % 4; Result: 1 |
Arithmetic operators can also be used in combination with SQL expressions which helps us perform complex calculations.
Example: SELECT (7 + 8) * 3 – 2;
Result: 43.
Comparison Operators
SQL comparison operators help us compare values and evaluate the conditions present which allow us to do logical comparisons and return a Boolean value as True or False. The common comparison operators used in SQL are:
| Operator | Description | Example |
|---|---|---|
| Equal (=) | Checks if two values are equal | SELECT * FROM students WHERE subject = ‘Maths’; |
| Not Equal (<> or !=) | Checks if two values are not equal | SELECT * FROM students WHERE balance <> 0; |
| Greater Than (>) | Checks if the left value is greater than the right value | SELECT * FROM students WHERE fine > 1000; |
| Less Than (<) | Checks if the left value is less than the right value | SELECT * FROM students WHERE fine < 1000; |
| Greater Than or Equal (>=) | Checks if the left value is greater than or equal to the right value | SELECT * FROM students WHERE fine >= 1000; |
| Less Than or Equal (<=) | Checks if the left value is less than or equal to the right value | SELECT * FROM students WHERE fine <= 1000; |
The comparison operators can be combined with logical operators, such as AND/ OR for the requirement of complex conditions and filtering the data.
Logical Operators
The logical operators in SQL combine the conditions to create more complex expressions and allow us to perform logical operations on Boolean conditions to get a Boolean result. It is mostly used with the WHERE clause for filtering the data through multiple conditions. Some of the commonly used logical operators are:
| Operator | Description | Example |
|---|---|---|
| AND | Combines multiple conditions, all of which must be true | SELECT * FROM student WHERE department = ‘Physics’ AND marks > 30; |
| OR | Combines multiple conditions, at least one must be true | SELECT * FROM student WHERE department = ‘Physics’ OR marks > 70; |
| NOT | Negates a condition, returning the opposite Boolean value | SELECT * FROM student WHERE NOT subject = ‘Biology’; |
The logical operators can be combined to achieve more complex conditions with grouping using parentheses. Example: SELECT * FROM marks WHERE (total_marks > 400 AND pass_status = ‘Pass’) OR (student_id = 123 AND exam_date >= ‘2023-06-04’);
String Operators
These operators are used for manipulating and comparing string values within SQL statements and enable us to concatenate strings to perform pattern matching, determine if the value falls within a specified range, and check for the presence of value in a list. The string operators are useful while working with textual data stored in a database. Some of the string operators in SQL are:
| Operator | Description | Example |
|---|---|---|
| Concatenation | Combines two or more string values into a single string | SELECT first_name || ‘ ‘ || last_name AS full_name FROM students; |
| LIKE | Used for pattern matching within a string using wildcard characters | SELECT * FROM products WHERE product_name LIKE ‘%apple%’; |
| IN | Checks if a value exists in the provided list of values | SELECT * FROM customers WHERE country IN (‘USA’, ‘Canada’, ‘Mexico’); |
| BETWEEN | Checks if a value lies within a specified range | SELECT * FROM students WHERE age BETWEEN 9 AND 16; |
NULL-related Operators
These operators are used for checking the NULL values within the SQL statements where NULL represents the absence of a value or the unknown data. These operators help us handle and identify NULL values in the query. Some of the common Null-related operators are:
| Operator | Description | Example |
|---|---|---|
| IS NULL | Checks if the value is NULL | SELECT * FROM students WHERE email IS NULL; |
| IS NOT NULL | Checks if a value is not NULL | SELECT * FROM students WHERE email IS NOT NULL; |
The NULL-related operators are very useful while dealing with the columns that allow NULL values or with data that has missing or unknown information.
Set Operators
The set operators in SQL help us combine the results of multiple SELECT statements and perform the required operations on the dataset. They help us combine rows from multiple queries, exclude specific elements from result sets or find common elements. Some of the common set operators in SQL are:
| Operator | Description | Example |
|---|---|---|
| UNION | Combines the result sets of two or more SELECT statements, removing duplicate rows from the combined result set | SELECT column1, column2 FROM table1 UNION SELECT column1, column2 FROM table2; |
| UNION ALL | Concatenates the result sets of two or more SELECT statements, including duplicate rows | SELECT column1, column2 FROM table1 UNION ALL SELECT column1, column2 FROM table2; |
| INTERSECT | Retrieves the common rows from the result sets of two or more SELECT statements | SELECT column1, column2 FROM table1 INTERSECT SELECT column1, column2 FROM table2; |
| EXCEPT | Retrieves the unique rows from the result set of the first statement that are not present in the second statement | SELECT column1, column2 FROM table1 EXCEPT SELECT column1, column2 FROM table2; |
Set operators have a high capability for combining, merging, and comparing data from multiple sources in SQL and help us create complex queries that involve multiple tables.
Aggregate Functions
It is used for performing calculations on a set of values and returns a single value as a result. They allow us to derive meaningful summary information and insights from the data and are used in combination with the GROUP BY clause that helps perform calculations on grouped data. Some of the common aggregate functions present in SQL are:
| Operator | Description | Example |
|---|---|---|
| Equal (=) | Checks if two values are equal | SELECT * FROM students WHERE subject = ‘Maths’; |
| Not Equal (<> or !=) | Checks if two values are not equal | SELECT * FROM students WHERE balance <> 0; |
| Greater Than (>) | Checks if the left value is greater than the right value | SELECT * FROM students WHERE fine > 1000; |
| Less Than (<) | Checks if the left value is less than the right value | SELECT * FROM students WHERE fine < 1000; |
| Greater Than or Equal (>=) | Checks if the left value is greater than or equal to the right value | SELECT * FROM students WHERE fine >= 1000; |
| Less Than or Equal (<=) | Checks if the left value is less than or equal to the right value | SELECT * FROM students WHERE fine <= 1000; |
These aggregate functions can be used to perform calculations on grouped data wing the GROUP BY clause. Example: SELECT department_id, AVG(salary) AS average_salary FROM employees GROUP BY department_id;
Conditional Operators
Conditional operators in SQL help us evaluate conditions and return different values for different actions based on results. These operators help us introduce control flow and logic into the SQL statements. Some of the common conditional operators in SQL are:
| Function/Statement | Description | Example |
|---|---|---|
| CASE Statement | Helps specify multiple conditions and defines different actions based on the evaluation of these conditions | SELECT product_name, CASE WHEN price < 100 THEN ‘Affordable’ WHEN price >= 100 AND price < 500 THEN ‘Moderate’ ELSE ‘Expensive’ END AS price_category FROM products; |
| COALESCE() Function | Returns the first non-null value from the available list of expressions | SELECT product_name, COALESCE(discounted_price, price) AS final_price FROM products; |
| NULLIF Function | Compares two expressions and returns null if they are equal, otherwise returns the first expression | SELECT product_name, NULLIF(in_stock_quantity, 0) AS available_quantity FROM products; |
Conclusion
In this blog, we have discussed the different types of SQL operators available and how they are used, and the condition to use these operators. SQL operators play a significant role that helps us manipulate data within the database and helps us perform complex operations, retrieve specific results, and filter data as per our requirements. SQL operators provide necessary tools for accomplishing multiple tasks which are discussed in the operators.
If you like the article and would like to support me, make sure to:
- 👏 Like for this article and subscribe to our newsletter
- 📰 View more content on my DataSpoof website
- 🔔 Follow Me: LinkedIn| Youtube | Instagram | Twitter
