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:

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

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:

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:

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:

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:

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.