13
DecCyber Monday Deal : Flat 30% OFF! + free self-paced courses - SCHEDULE CALL
SQL stands for Structured Query Language, which is a relational database management system developed by Microsoft. With the help of SQL Server, we can retrieve or store the data from one place to another called database, and it is further can be integrated with programming apps as well. SQL works similarly to other popular relational database management systems like Sybase, MySQL, PL/SQL, etc.
SQL was introduced in 1989, and the original code of SQL was sold by Sybase SQL Server to Microsoft. It was introduced in the competition of IBM, Oracle, etc. Today, there are more than 14 versions of the SQL server introduced by Microsoft with additional features and enhancements. In this blog for SQL operators, we will mainly discuss SQL comparison operators, SQL arithmetic functions, SQL operations, SQL logical operators, and more.
Read: Different Types of SQL Keys: Example and Uses
The objective of this blog is to discuss SQL operators and how various comparison operators can be used to perform specific actions on one or more expressions.
Learn SQL Server in the Easiest Way
In SQL, an operator is a symbol that is used to specify a particular action performed on one or more expressions. With the help of these SQL operators, you can perform SQL arithmetic functions, logical, or comparison SQL operations on different expressions. In this blog for SQL Operators, we will focus on SQL comparison operators in detail and others will be given in brief only.
SQL Arithmetic Functions are used to perform multiple mathematical operations like addition, subtraction, division, multiplication, modulus, etc. A range of SQL arithmetic functions can be given as:
SQL Operators | SQL Operators Description | SQL Operators Example |
Addition (+) | To add two or more numbers, we can use this operator. | 16+5=21 |
Subtraction (-) | To subtract two or more numbers, we can use this operator. | 16-5=11 |
Multiplication (*) | For multiplication of numbers, we can use this operator. | 16*5=80 |
Division (/) | To divide numbers, we can use this operator. | 16/5=3 |
Modulus (%) | This operator returns the remainder of a division operation. | 16/5=1 |
SQL Logical Operators are used to perform multiple operations like checking SQL statements based on different conditions like AND, OR, Between, Exists, etc. they can also be used to compare multiple values in SQL statements.
Operators | Description |
AND | It is used to compare data with one or more conditions, if all conditions are TRUE, only then the final result is displayed. |
OR | It is used to compare data with one or more conditions; if either of the conditions is TRUE, the final value is displayed. |
LIKE | It is used to search for character strings with similar patterns using different wildcards. |
ALL | It sets TRUE when all values match the given values in a single set of columns. It works almost similar to the AND operator. |
ANY | It sets TRUE when any value matches the given values in a single set of columns. It works almost similar to the OR operator. |
IN | It is used to search for specified values matches any value in a set of multiple values. |
BETWEEN | It is used to find values within a given range. |
EXISTS | It is used to show results when the subquery returns the data. |
NOT | It Sybase SQL Server sold the original code of SQL as negation operator in SQL that returns the opposite result as defined by the condition. |
SOME | It is used to compare values with a single set of columns as returned by the subquery. |
These operators will assign required values to variables. There is only one assignment operator available, Equal to (=).
Request for a demo class now to upgrade your SQL fundamental skills.
SQL Server Training & Certification
SQL comparison operators are used to compare multiple expressions based on given condition with the help of different comparison operators like Equals to (=), Greater than (>), Less than (<), Not equal to (<>), etc.
Here is a quick table to give the exact idea of comparison operators in SQL.
Operators | Description |
EQUAL TO (=) | It will ether two expressions are the same or not. If it is the same, then matched records are displayed. |
NOT EQUAL TO (! = ) | It will check whether two expressions are similar or not. If they are not the same, then non-matched records are displayed. |
Greater Than (>) | It will check either if one expression is greater than the other, if yes then the final value is displayed. |
Less Than (<) | It will check either one expression is lower than the other, if yes then the final value is displayed. |
NOT EQUAL TO (< >) | It will check whether two expressions are the same or not. If they are not the same, then non-matched records are displayed. |
Greater Than or Equal To (>=) | It will check either one expression is greater than or equal to another expression, if yes then the final value is displayed. |
Less Than or Equal To (<=) | It will check either one expression is lower than or equal to another expression, if yes then the final value is displayed. |
Not Greater Than (!>) | It will check either one expression is greater than another expression; if it is not greater, then the final value is displayed. |
Not Less Than (!<) | It will check either one operator is lower than another expression; if it is not lower, then the final value is displayed. |
For any expression that returns a Boolean data type in the end, it is a Boolean Expression. A boolean data type generally returns two values either 0 or 1, TRUE or FALSE. The final output of a comparison operator is a Boolean data type.
It will check the equality of two expressions in SQL. The basic syntax of this comparison operator can be given as:
Expression = expression
Here, you have to put valid values and compare then using “Equal To” operator. every time you are comparing two expressions, make sure that they have the same data type otherwise make them similar using data precedence technique and compare them later. The final output for this expression is a Boolean data type because if values are same it will return TRUE or 1. In case, values are not the same then it will return FALSE or 0.
Read: What is Composite Key in SQL? Composite Primary & Foreign Key Explain with Example
Example: Let us consider this simple example that will return records where employee ID is 1.
SELECT * FROM EmployeeDetails WHERE empid = 1
On the execution of this query, you will get the following result:
Empid | Empname | Designation | Salary | Location | Joineddate |
1 | Suresh | Software Engineer | 25000 | Chennai | 1986-05-20 00:00:00.000 |
It works opposite to the Equal to Comparison operator. IF both expressions are the same then it will return FALSE otherwise TRUE. In this case, the final return value is a Boolean data type. The basic syntax for Not Equal To cis given below.
Expression <> expression
If both expressions are NULL, then the final value is also NULL. Otherwise, it will return a non-NULL value for Non-NULL values. You can use any valid expression that you want to compare. Keep in mind that both expressions should have the same data type. If data types are not the same then convert any one of them using “data type precedence technique.” The final output if a Boolean value for this expression.
Read: Different Type of SQL Joins
Example: Consider this example that will return records where employee ID is not equal to 1.
SELECT * FROM EmployeeDetails WHERE empid <> 1
On the execution of this query, you will get the following result:
Empid | Empname | Designation | Salary | Location |
2 | Rohini | AEO | 15000 | Chennai |
3 | Madhavsai | Business analyst | 50000 | Nagpur |
4 | Mahendra | CA | 75000 | Guntur |
5 | Sateesh | Doctor | 65000 | Guntur |
It is again used to compare two expressions. IF both expressions are the same then it will return FALSE otherwise TRUE. In this case, the final return value is a Boolean data type. Example: Consider this example that will return records where employee ID is not equal to 1.
SELECT * FROM EmployeeDetails WHERE empid <> 1
On the execution of this query, you will get the following result:
Read: SSRS Pie Chart - Having a Slice of the Pie
Empid | Empname | Designation | Salary | Location |
2 | Rohini | AEO | 15000 | Chennai |
3 | Madhavsai | Business analyst | 50000 | Nagpur |
4 | Mahendra | CA | 75000 | Guntur |
5 | Sateesh | Doctor | 65000 | Guntur |
Don’t get confused between two operators “<>” and “!=” that are used to perform inequality test in SQL and always returns the similar output. The major difference between both operators is that “<>” does follow the ISO standard while “!=” does not follow any ISO standard. So, it would be great using “<>” operator in comparison to another operator for avoiding any issues.
It is used to check either Left-hand operator is greater than the Right-Hand operator, if yes then the final value is displayed. The basic syntax of this operator can be given as:
Expression > expression
Example: Consider this example that will return records where employee ID is greater than 2.
Read: Learn SQL Union All Query Operators with Examples
SELECT * FROM EmployeeDetails WHERE empid > 2
On the execution of this query, you will get the following result:
Empid | Empname | Designation | Salary | Location |
3 | Madhavsai | Business analyst | 50000 | Nagpur |
4 | Mahendra | CA | 75000 | Guntur |
5 | Sateesh | Doctor | 65000 | Guntur |
You can use any valid expression that you want to compare. Keep in mind that both expressions should have the same data type. If data types are not the same, then convert any one of them using “data type precedence technique.” The final output if a Boolean value for this expression.
This operator will check either the expression at one side is less than the expression at the other side, if yes then the final value is displayed. The basic syntax of this expression can be given as:
Expression < expression
Example: Consider this example that will return records where employee ID is less than 2.
Read: The Database as Part of the System Architecture
SELECT * FROM Employeedetails WHERE empid
On the execution of this query, you will get the following result:
Empid | Empname | Designation | Salary | Location |
1 | Suresh | Software engineer | 25000 | Chennai |
This comparison operator will check either the expression at one side is greater than or equal to the expression at the other side, if yes then it will display the final value. The basic syntax of the query can be given as:
Expression >= expression
Example: Consider this example that will return records where employee ID is greater than or equal to 2.
Read: A Comprehensive SQL Server Tutorial Guide for Beginners & Experienced
SELECT * FROM EmployeeDetails WHERE empid >= 2
On the execution of this query, you will get the following result:
Empid | Empname | Designation | Salary | Location |
2 | Rohini | AEO | 15000 | Chennai |
3 | Madhavsai | Business analyst | 50000 | Nagpur |
4 | Mahendra | CA | 75000 | Guntur |
This operator will check either the expression at one side is lower than or equal to the expression at the other side. The basic syntax of this query can be given as:
Expression <= expression
Example: Consider this example that will return records where employee ID is less than or equal to 2.
Read: How to Create Table in SQL Server by SQL Query?
SELECT * FROM EmployeeDetails WHERE empid <= 2
On the execution of this query, you will get the following result:
Empid | Empname | Designation | Salary | Location | Joineddate |
1 | Suresh | Software engineer | 25000 | Chennai | 1986-05-20 00:00:00.000 |
2 | Rohini | AEO | 15000 | Chennai | 1987-01-04 00:00:00.000 |
This operator will check either the expression at one side is greater than the expression at the other side; if it is not greater, then the final value is displayed. The basic syntax of this operator can be given as:
Expression !> expression
Example: Consider this example that will return records where employee ID is not greater than 2.
Read: SQL Server DBA Roles and Responsibilities: What Should You Know?
SELECT * FROM EmployeeDetails WHERE empid !> 2
On the execution of this query, you will get the following result:
Empid | Empname | Designation | Salary | Location |
1 | Suresh | Software engineer | 25000 | Chennai |
2 | Rohini | AEO | 15000 | Chennai |
This operator will check either the expression at one side is lower than the expression at the other side; if it is not lower, then the final value is displayed. The basic syntax of this operator can be given as:
Expression !< expression
Example: Consider this example that will return records where employee ID is not less than 2.
SELECT * FROM EmployeeDetails WHERE empid !
On the execution of this query, you will get the following result:
Empid | Empname | Designation | Salary | Location |
2 | Rohini | AEO | 15000 | Chennai |
3 | Madhavsai | Business analyst | 50000 | Nagpur |
4 | Mahendra | CA | 75000 | Guntur |
SQL Server Training & Certification
This blog gives you a sound idea of SQL operators, why we use them, and different types of SQL comparison operators in detail with examples. We have also discussed SQl operations with description and example, SQL arithmetic functions. You can practice them one by one and understand the difference. Don’t forget to experiment using different values to make the concept clear to you. If you are still not sure or want to use these operators for complex queries, then join the SQL Server certification program at JanBask Training and master all SQL server concepts in detail to use them in the workplace. All the Best!
Read: MySQL vs SQL Server vs Oracle: A Detailed ComparisonFaceBook Twitter LinkedIn Pinterest Email
A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Search Posts
Related Posts
Receive Latest Materials and Offers on SQL Server Course
Interviews