SQL Operators with Syntax and Examples

FREE Online Courses: Click for Success, Learn for Free - Start Now!

We will discuss SQL(Structured Query Language) operators in this article.

In SQL, we have multiple types of operators available, which we will be discussing shortly. We use SQL operators to specify conditions and statements, satisfying a query of desired data output.

What is SQL Operator?

An operator is a reserved character or word which is used in a SQL statement to query our database. We use a WHERE clause to query a database using operators.

Operators are needed to specify conditions in a SQL statement. The available operators act as a connector for various conditional statements.

Types of SQL Operators

We have various SQL operators, and they are as follows:

  • SQL Arithmetic operators
  • SQL Comparison operators
  • SQL Logical operators
  • SQL Compound Operators
  • SQL Bitwise Operators
  • SQL Unary Operator

Demo Database

Let us first view our database, which we will use in this tutorial; we will be using a database of DataFlair employees and their details. Our database is as follows :

Query:

SELECT * FROM DataFlair_Employee ;

SQL Demo database

This is what our database looks like. Let’s get ahead and learn about SQL operators.

SQL Arithmetic Operators

Syntax: 

SELECT column1+column2(airthmetic operation on columns),
column2*column3 (airthmetic operation on columns),
……
FROM tableName;

These are basic day-to-day operators used in SQL queries. All the arithmetic operators are shown in the table below:

Sr.NoArithmetic OperatorsDescription
1+Add
2Subtract
3*Multiply
4/Division
5%Modulo

Example 1: Let us check if we double the Salaries of our employees at DataFlair using the SQL query.
Solution 1: Using the addition operator.
Query: 

SELECT name_emp, salary+salary as Double_Salary FROM DataFlair_Employee ;

airthmetic operators example 1

We can clearly see what would be the salary if we double the salary of the DataFlair employees.

Solution 2: Using the multiplication operator.
Query:

SELECT name_emp, salary*2 as Double_Salary FROM DataFlair_Employee ;

airthmetic operator example 2

We can clearly see what would be the salary if we double the salary of the DataFlair employees.

SQL Comparison Operators

Sr.NoComparison OperatorsDescription
1=Equal
2>Greater than
3<Smaller than
4>=Greater than Equal to
5<=Smaller than Equal to
6!<Not less than
7!=Not Equal to
8! >Not greater than
9< >Value equal to

Syntax of SQL Comparison Operators:

SELECT column1>column2(comparisons ofcolumns),
column2<column3 (comparisons of columns),
……
FROM tableName;

Example 1: Let us view employees with a salary of less than 20,000.
Query:

SELECT name_emp, salary FROM DataFlair_Employee where salary<20000;

SQL Comparison Operators Example

We can observe the employees with a salary of less than 20,000 in DataFlair.

Example 2: Let us view the details of employees whose age is more than or equal to 25.
Query: 

SELECT * FROM DataFlair_Employee where age >= 25 ;

SQL comparison operator example 2

SQL Compound Operators

SQL compound operators are as shown below in the following table:

Sr.NoCompound OperatorsDescription
1+=Add equals
2-=Subtract equals
3*=Multiply equals
4/=Divide equals
5%=Modulo equals
6&=Bitwise AND equals
7^-=Bitwise Exclusive equals
8|*=Bitwise exclusive OR equals

Syntax of Compound Operators in SQL:

SELECT column1+=column2 (compound operations on columns),
column2*=column3 (compound operations on columns),
……
FROM tableName;

Example: Let us increment the age of each employee with 10.
Query:

Select (age+=10) from dataflair ;

example of SQL compound operator

SQL Logical Operators

SQL provides us with many logical operators to use while querying a database. All the logical operators are listed below in the table:

Sr.NoSQL Logical OperatorsDescription
1ALLReturns TRUE when all subqueries are satisfied
2ANDReturns TRUE if all conditions in AND are satisfied
3ANYReturns TRUE if any subquery is satisfied
4BETWEENReturns TRUE if the operand is in the given range
5EXISTSReturns true if one or more data record exists
6INReturns TRUE if data matches the list of conditions
7LIKEReturns TRUE if our operand is similar to a pattern
8NOTReturns records if the condition is not satisfied
9ORReturns TRUE if any one of all subqueries is satisfied
10SOMEReturns TRUE if any of our subqueries is satisfied.
11IS NULLUsed to compare the NULL values.
12UNIQUEReturns rows that are unique.

Syntax of Logical Operators in SQL: 

SELECT column1,
Column2,
column3,
……
FROM tableName
WHERE logical condition ;

Example 1: Let us find employees whose age is greater than 27 and salary is greater than 25,000.
Query:

SELECT name_emp,salary, age  FROM DataFlair_Employee WHERE age>27 AND salary>25000;

SQL logical operators example 1

Example 2: Let us find employees whose salary is less than 25 or the salary is more than 27,000.
Query:

SELECT name_emp,salary, age  FROM DataFlair_Employee WHERE age<25 OR salary>27000;

SQL logical operators example 2

SQL Unary Operators

SQL unary operators operate in a single operand and can be used on any numeric datatype. SQL unary operators are as follows:

Sr.NoUnary Operators in SQLDescription
1(-)Returns the negative value of the passed expression.
2(+)Returns the positive value of the passed expression.

Syntax of SQL Unary Operators:

SELECT -(col1),+(col2), ... FROM tablename;

Example:
Let us find the -ve age of Siya.
Query: 

Select -(age) as negAge from dataflair where name ='Siya';

example of SQL unary operator

SQL Bitwise Operators

SQL bitwise operators are as below shown in the table:

Sr.NoBitwise Operators in SQLDescription
1&Bitwise AND
2|Bitwise OR
3^Bitwise exclusive OR
4<<Left Shift
5>>Right Shift

Syntax of Bitwise Operators in SQL:

SELECT (col1 & num1), (col2 | num2), ….. FROM tableName;

Example: Let us find the & of employees age from 10.
Query:

Select (age & 10) from dataflair ;

example of bitwise operator in SQL

Summary

We have discussed and analyzed how to use various SQL operators in queries, to get the desired output data as required by the user.

SQL operators come in handy when we need to cleanse and prepare data for data analysis. We use queries to transact data when a user asks for data that satisfies some conditions.

Operators make it easy for us to obtain the required data values from the database in the most efficient and orderly method from the database records.

We work very hard to provide you quality material
Could you take 15 seconds and share your happy experience on Google

follow dataflair on YouTube

2 Responses

  1. Mohamed says:

    Correction

    : Not Equal Operator

Leave a Reply

Your email address will not be published. Required fields are marked *