Today's Offer - SQL Server Certification Training - Enroll at Flat 10% Off.

- SQL Server Blogs -

SQL Operators you Need to Know

SQL stands for Structured Query Language that 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 similar 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 SQL server introduced by Microsoft with additional features and enhancements.

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 operators, you can perform arithmetic, logical, or comparison operations on SQL expressions. In this blog, we will focus on comparison operators in detail and others will be given in brief only.

Operators in SQL

SQL Arithmetic Operators

SQL Arithmetic Operators are used to perform multiple mathematical operations like addition, subtraction, division, multiplication, modulus, etc. A range of SQL arithmetic operators can be given as:

Operators Description 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 (=).

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.

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.

Example: Let us consider this simple example that will return records where employee ID is 1.

Read: Most Popular SQL Server Performance Tuning Tips

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

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.

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

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:

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.

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: SQL Intersect Operator With Example

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.

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.

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

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.

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

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

Read: What Is The Difference Between Tables And Views In SQL?

Example: Consider this example that will return records where employee ID is less than or equal to 2.

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

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.

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

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

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. 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: SSRS Interview Questions & Answers For Experienced

    Janbask Training

    JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.


Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

2 days 24 Nov 2019

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

3 days 25 Nov 2019

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

3 days 25 Nov 2019

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

4 days 26 Nov 2019

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

12 days 04 Dec 2019

Course for testing

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

32 days 24 Dec 2019

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

11 days 03 Dec 2019

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

3 days 25 Nov 2019

SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

0 day 22 Nov 2019

Comments

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews