SQL Operators with Syntax and Examples

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 ;

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.No	Arithmetic Operators	Description
1	+	Add
2	–	Subtract
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 ;

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 ;

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

SQL Comparison Operators

Sr.No	Comparison Operators	Description
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;

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

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

Sr.No	Compound Operators	Description
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 ;

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.No	SQL Logical Operators	Description
1	ALL	Returns TRUE when all subqueries are satisfied
2	AND	Returns TRUE if all conditions in AND are satisfied
3	ANY	Returns TRUE if any subquery is satisfied
4	BETWEEN	Returns TRUE if the operand is in the given range
5	EXISTS	Returns true if one or more data record exists
6	IN	Returns TRUE if data matches the list of conditions
7	LIKE	Returns TRUE if our operand is similar to a pattern
8	NOT	Returns records if the condition is not satisfied
9	OR	Returns TRUE if any one of all subqueries is satisfied
10	SOME	Returns TRUE if any of our subqueries is satisfied.
11	IS NULL	Used to compare the NULL values.
12	UNIQUE	Returns 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;

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 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.No	Unary Operators in SQL	Description
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';

SQL Bitwise Operators

SQL bitwise operators are as below shown in the table:

Sr.No	Bitwise Operators in SQL	Description
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 ;

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.