SQL Operators You Need to Know

Introduction

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: Snapping a Picture of a Database-Database snapshot

Uses of SQL Server

• You can access data from the database and perform different operations on it.
• You can create databases, stored procedures, tables to access data in different formats.
• You can insert, delete, or select data to communicate with the stored data in databases.
• You can create views, functions, keys, indexes, etc. to access the data more efficiently.
• You can create roles and define permissions at different user-levels to access data in databases.
• You can access data using SQL modules or libraries.
• Most importantly, you can use operators to perform actions on one or more expressions.

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.

Operators in SQL

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

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

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.

SQL Assignment Operators

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 Comparison Operators

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.

Boolean Data Types

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.

1). Equal To (=) Operator in SQL

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: Most Frequently Asked RDBMS Interview Questions And Answers

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

2). Not Equal To (< >)

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: What Does It Take To Become a Successful SQL DBA Expert?

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

3). Not Equal To (!=)

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: What Does It Take To Become a Successful SQL DBA Expert?

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.

4). Greater Than (>) Operator in SQL

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: How to Restore a Database Backup from SQL Server Management Studio

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.

5). Less Than Operator (<) in SQL

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: SSRS Sub Reports and deployment process-How to do it

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

6). SQL Greater Than or Equal To (>=) Operator

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: SQL Server Analysis Services – All You Need to Know

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

7). SQL Less Than or Equal To (<=) Operator

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: What is the Difference Between Control Flow & Data Flow in SSIS?

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

8). SQL Not Greater Than (!>) Operator

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: What is Foreign Key in SQL? How to Set, Add, Create & Use of Foreign Key

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

9). SQL Not Less Than (!<) Operator

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

Final Words

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: What is Primary Key in SQL? How to Add, Remove, Or Set Primary Key

FaceBook Twitter LinkedIn Pinterest Email