Summary -

In this topic, we described about the below sections -

What are SQL Operators?

SQL Operators are keywords or characters, used to perform various operations on different types of data items and returns the result.

Need of SQL Operators?

SQL Operators are used in WHERE clause to specify the condition. SQL Operators acts as conjunction for multiple conditions in one Statement. By using SQL Operators, we can select particular record in table based on our requirement.

Types of SQL Operators

SQL Operators are classified into different types as described below:

  • Arithmetic Operators
  • Character Operators
  • Comparison Operators
  • Logical Operators
  • Bitwise Operators
  • Set Operators

Arithmetic Operators

These operators are used to perform operations on the columns that are defined as numeric or numeric related data type. These operators can perform addition, subtraction, multiplication, division etc,

The list of arithmetic operators are specified below -

Operator Operation Name Description
+ (Unary) Unary Addition Makes Operand positive
-(Unary) Unary Minus Makes operand negative
+ Subtraction Subtract the right-hand operand from the left-hand operand.
* Multiplication Multiply the values of both operands.
/ Division Divide the left-hand operand by the right-hand operand and gives the result.
% Modulus Divide the left-hand operand by the right-hand operand and returns the remainder.

Example -

Below table shows how the arithmetic operators used in SQL -

Operator Operation Name Usage Result
+ (unary) Unary Plus 
SELECT +3 FROM DUAL;
+3
-(unary) Unary Minus 
SELECT -3 FROM DUAL;
-3
+ Addition 
SELECT 22 + 33;
55
- Subtraction 
SELECT 33 – 22;
11
* Multiplication 
SELECT 11 * 11;
121
/ Division 
SELECT 52 / 5;
10
% Modulus 
SELECT 52 % 5;
2

Character Operators

Character operators are used to concatenate the two strings or a string with column values.

The list of character operators are specified below –

Operator Operation Name Description
|| Concatenate Operator Concatenate the two strings or a string with column values.

Example -

Consider an employee table employee_details with 9 employee's information.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-11-07 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Operator Operation Name Usage Result
|| Concatenate Operator SELECT "Employee Name is " || ename FROM employee_details WHERE salary = 45000; Employee Name is Employee1

Comparison Operators

Comparison operators are used to validate the column with the value to get the results.

These operators can perform operations such as equal, not equal greater than, less than etc,.

The list of comparison operators are specified below -

Operator Operation Name Description
= Equal Checks if column values are equal to the specified value, then matched results get displayed. If no matches, then no results displayed.
!= Not Equal Checks if column values are not equal to the specified value, then matched results get displayed. If no matches, then no results displayed.
< > Not Equal Checks if column values are not equal to the specified value, then matched results get displayed. If no matches, then no results displayed.
> Greater than Checks if column values are greater than to the specified value, then matched results get displayed. If no matches, then no results displayed.
< Less than Checks if column values are less than to the specified value, then matched results get displayed. If no matches, then no results displayed.
>= Greater than or equal to Checks if column values are greater than or equal to the specified value, then matched results get displayed. If no matches, then no results displayed.
<= Less than or equal to Checks if column values are less than or equal to the specified value, then matched results get displayed. If no matches, then no results displayed.
!> Not greater than Checks if column values are not greater than to the specified value, then matched results get displayed. If no matches, then no results displayed.
!< Not less than Checks if column values are not less than to the specified value, then matched results get displayed. If no matches, then no results displayed.
IN Compares with a specified list of values.
BETWEEN Searches the value within the values specified.
ALL Compares the value with all the values in the value set.
ANY Compares the value against any value in the values set.
EXISTS Searches the presence of the row in the table and sets TRUE if sub query gets single row.
LIKE Searches the similar words using wildcard operator.
SOME Similar to ANY operator. However, the search is restricted to some of the values present in the set of values.
IS NULL Compares a value with a NULL.

Example -

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-11-07 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 24000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Below table shows how the comparison operators used in SQL to get the employee names (ename) based on the conditions -

Operator Usage Result
= SELECT ename FROM employee_details WHERE designation = "Director"; Employee1, Employee2
!= SELECT ename FROM employee_details WHERE salary != 60000; Employee2, Employee3, Employee4, Employee5, Employee6, Employee7, Employee8, Employee9
<> SELECT ename FROM employee_details WHERE salary <> 60000; Employee2, Employee3, Employee4, Employee5, Employee6, Employee7, Employee8, Employee9
> SELECT ename FROM employee_details WHERE salary > 40000; Employee1
< SELECT ename FROM employee_details WHERE salary < 40000; Employee3, Employee4, Employee5, Employee6, Employee7, Employee8, Employee9
>= SELECT ename FROM employee_details WHERE salary >= 40000; Employee1, Employee2
<= SELECT ename FROM employee_details WHERE salary <= 40000; Employee2, Employee3, Employee4, Employee5, Employee6, Employee7, Employee8, Employee9
!> SELECT ename FROM employee_details WHERE salary !> 40000; Employee2, Employee3, Employee4, Employee5, Employee6, Employee7, Employee8, Employee9
!< SELECT ename FROM employee_details WHERE salary !< 40000; Employee1, Employee2
IN SELECT ename FROM employee_details WHERE designation IN ("Manager", "Director"); Employee1, Employee2, Employee3,Employee4
BETWEEN SELECT ename FROM employee_details WHERE salary BETWEEN 24000 AND 35000; Employee3,Employee4
ALL SELECT ename FROM employee_details WHERE salary = ALL (25000, 60000); Employee1, Employee2, Employee3, Employee4
ANY SELECT ename FROM employee_details WHERE salary = ANY (25000, 60000); Employee1, Employee4
EXISTS SELECT ename FROM employee_details WHERE EXISTS (SELECT salary ename FROM employee_details WHERE salary > 40000); Employee1
LIKE SELECT ename FROM employee_details WHERE designation LIKE "%ger"; Employee3, Employee4
SOME SELECT ename FROM employee_details WHERE salary > SOME (25000, 50000); Employee1, Employee2,Employee3
IS NULL SELECT ename FROM employee_details WHERE designation IS NULL Empty

Logical Operators

Logical operators are used to combine multiple conditions or validate multiple against multiple values to get the results. These operators can perform operations such as AND, OR, BETWEEN, IN, NOT etc,.

The list of logical operators are specified below -

Operator Description
AND Allows or combines multiple conditions in an SQL statement.
OR Combines multiple conditions in SQL Statement.
NOT Applies on logical operators only. Reverses the meaning of any logical operators.

Example -

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

eid ename designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 15000.00 2000

Below table shows how the logical operators used in SQL to get the employee names (ename) based on the conditions -

Operator Usage Result
AND SELECT ename FROM employee_details WHERE
designation = "Manager" AND salary > 25000; 		Employee3
OR SELECT ename FROM employee_details WHERE
designation = "Analyst" OR salary > 27000; 		Employee1, Employee2,
Employee5, Employee6
NOT SELECT ename FROM employee_details WHERE
designation NOT IN ("Manager", "Salesman"); 		Employee3, Employee4,
Employee8, Employee9

Bitwise Operators

These Operators are used to perform on integer values only.

Bitwise operators are used to perform bit operation on the integer values to get the results.

These operators can perform operations such as Bitwise AND, Bitwise OR etc.

The list of bitwise operators are specified below –

Operators Description
Bitwise AND (&) The numerical values are converted into binary values and operation is performed, result again converted into numerical form.
Bitwise OR (|) The numerical values are converted into binary form, then or operation is performed.
Bitwise exclusive OR (^) The numerical values converted into binary form, then Exclusive or operation is performed.

Set Operators

Set operators are used to combine the results of two queries into a single result.

The list of set operators are specified below -

Operators Description
UNION Select all distinct rows selected by either query.
UNION ALL Returns all the rows selected from two rows including duplicates.
INTERSECT or
INTERSECT ALL		 Returns all distinct rows selected by both rows.
MINUS Returns all distinct rows selected by the first query but not from the second one.

Example -

employee_details -

eid ename designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Below table shows how the set operators used in SQL to get the employee names (ename) based on the conditions -

Operator Usage Result
UNION SELECT ename FROM employee_details WHERE salary > 20000
UNION
SELECT ename FROM employee_details WHERE designation = "Manager"; 		Employee1,Employee2, Employee3, Employee4
UNION ALL SELECT ename FROM employee_details WHERE salary > 20000
UNION ALL
SELECT ename FROM employee_details WHERE designation = "Manager"; 		Employee1, Employee2, Employee3,
Employee4, Employee3,Employee4
INSPECT SELECT ename FROM employee_details WHERE salary > 20000
INSPECT
SELECT ename FROM employee_details WHERE designation = "Manager"; 		Employee3, Employee4
INSPECT ALL SELECT ename FROM employee_details WHERE salary > 20000
INSPECT ALL
SELECT ename FROM employee_details WHERE salary > 30000; 		Employee3, Employee4
MINUS SELECT ename FROM employee_details WHERE salary > 20000
MINUS
SELECT ename FROM employee_details WHERE salary > 30000; 		Employee1, Employee2