Operators in SQL

The Operators are the individual items that manipulate data and return the results. Here the data items are operands. The query which works on the operands is called operators. The operators are represented in the special characters or the keywords.

The Oracle SQL provides the following types of operators.

  • Unary and binary operators
  • Arithmetic operators
  • Comparison operators
  • Logical operators
  • Bitwise operators

Unary and Binary operators in SQL

Unary operators and the Binary operators are the types of operators in Oracle SQL. These are the two general classes in the operators. Let us study them in detail.

Unary Operator in SQL:

An operator that operates on the single operand is known as Unary operators. We can use both '-' and '+' symbol in the Unary operator.

Syntax of the Unary operator :

Operator Operand
  • Operator : The sign which indicates what kind of operation should be done
  • Operand : The value on which the operation should be done.

Example of the Unary operator :

SELECT -123 
FROM dual;

In this query, only one operand and the operator are present. Here, the '-' operator is represented first and the '123' operand is represented next. This operator is used to change the sign of the operand.
sql-unary-operator

The Binary operator in SQL:

The operator that operates on the two operands is known as Binary operator. We can use both '-' and '+' symbol in the Binary operator.

Syntax of the Binary operator :

Operand1 Operator Operand2
  • Operand1: The first value on which the operation should be done.
  • Operator : The sign which indicates what kind of operation should be done
  • Operand2: The second value on which the operation should be done.

Example of the Binary operator :

SELECT 382-123 
FROM dual;

In this query, There are two operands. They are 382 and 123. The operator is '-'. The operator '-' sign instructs the number 123 to be subtracted from the number 382.

binary-operator-sql

The arithmetic operator in SQL

The operators which can perform the arithmetical operations on numerical operands are called Arithmetic operators. Some operators of arithmetic operators are as follows:

  • + : Addition
  • - : Subtraction
  • *: Multiplication
  • / : Division

The addition operator adds the two operands. The symbol of this operator is '+'. This operator is similar to the binary operator.

Example of the addition operator for the DUAL table :

SELECT (2+3) Addition
FROM dual;

In this query, the numbers 2 and 3 are added.
addition-operator-dual-sql

Example of the addition operator for the user-created table :

UPDATE persons_table
SET salary=salary+1000;

In this query, the person_id, first_name, last_name, salary columns are selected. And the salary has been increased by 5000. The columns are selected where the salary is greater than the salary+5000.
add-operator-update-sql
data-before-operation
updated-add-operator-sql

The subtraction operator subtracts the two operands. The symbol of this operator is '-'. This operator is also a part of the binary operator.

Example of subtraction operator for the DUAL table :

SELECT (10-5) Substraction
FROM dual;

In this query, the number 10 is subtracted by the number 5.
substraction-operator-dual-sql

Example of the subtraction operator in the user-created table :

UPDATE persons_table
SET salary=salary+1000;

In this query, the first_name, salary columns are selected. And the salary has been reduced by 5000.
update-sub-operator-sql
data-before-operation
updated-subtraction-operator-sql

The multiplication operator multiplies the two operands. The symbol of this operator is the '*'.

Example of the Multiplication operator for the DUAL table :

SELECT (10*4) Multiplication
FROM dual;

In this query, the number 10 is multiplied by the number 4.
multiplication-operator-dual-sql

Example of the Multiplication operator for the user-created table :

UPDATE persons_table
SET salary=salary*2;

In this query, the first_name, last_name, salary columns are selected. And the salary has been multiplied by 15. The columns are selected where the salary is greater than the salary*15.
update-mul-operator-sql
data-before-operation
updated-mul-operator-sql

Division operator in SQL:

The Division operator divides the two operands. The symbol of this operator is '/'.

Example of the division operator for the DUAL table :

SELECT (10/5) Division
FROM dual;

div-operator-dual-sql

Example of the division operator for the user-created table :

UPDATE persons_table
SET salary = salary / 2;

In this query, the salary is divided by 1.1 and is updated in the persons_table.
update-div-operator-sql
data-before-operation

The below image shows the updated salary which is divided by 2.
updated-div-operator-sql

Comparison operators in SQL

The operator which compares the values and returns TRUE or FALSE is known as the comparison operator. Some commonly used comparison operators are as follows,

  • = : Equal
  • >: Greater than
  • < : Lesser than
  • >=: Greater than or equal
  • >=: Lesser than or equal
  • <>,!=,~= : Not equal

The equal operator in SQL:

The equal operator compares the values on the left-hand side and the right-hand side and results in TRUE if LHS is equivalent to RHS. The symbol of this operator is '='.

An example of an equal operator is :

SELECT gender, age, salary, city
FROM persons_table
WHERE gender='f';

In this query, the columns are selected according to gender. In which the gender is equal to the 'f'.
equal-operator-sql

The greater-than operator in SQL:

The greater than operator compares the values on the left-hand side and the right-hand side and results in TRUE if LHS is greater than the RHS. The symbol of this operator is '>'.

SELECT first_name, age, salary
FROM persons_table
WHERE age > 30;

In this query, the columns first_name, age, salary are selected where the age is greater than 30.
greater-than-operator-sql

The lesser than the operator in SQL:

The lesser than the operator compares the values on the left-hand side and the right-hand side and results in TRUE if LHS is lesser than the RHS. The symbol of this operator is '<'.

SELECT first_name, age, salary
FROM persons_table
WHERE age < 30;

In this query, the columns first_name, age, salary are selected where the age is lesser than 30.
lesser-than-operator-sql

The greater than or equal operator in SQL:

The greater than or equal operator compares the values on the left-hand side and the right-hand side and results in TRUE if LHS is greater than or equal to the RHS. The symbol of this operator is '>='.

SELECT first_name, age, salary
FROM persons_table
WHERE salary >= 30000;

In this query, the columns first_name, age, salary are selected where the salary is greater than or equal to 30000.

greater-than-or-equal-operator-sql

The lesser than or equal operator in SQL:

The lesser than or equal to operator compares the values on the left-hand side and the right-hand side and results in TRUE if LHS is lesser than or equal to the RHS. The symbol of this operator is '<='.

SELECT first_name, age, salary
FROM persons_table
WHERE salary <= 30000;

In this query, the columns first_name, age, salary are selected where the salary is lesser than or equal to 30000.

lesser-than-or-equal-operator-sql

The not equal operator in SQL:

The not equal operator compares the values on the left-hand side and the right-hand side and results in TRUE if LHS is not equivalent to RHS. The symbol of this operator is '!=' or '<>' or '~='.

SELECT first_name, age, salary,city
FROM persons_table
WHERE city <> 'Sakleshpur';

In this query, the columns first_name, age, salary, city are selected where the city is not equal to 'Sakleshpur'.

not-equal-operator-sql

Logical operators in SQL

The Logical operators' results true or false by comparing the multiple conditions. The commonly used logical operators are,

  • AND
  • ALL
  • OR
  • ANY
  • NOT
  • BETWEEN
  • IN
  • EXISTS
  • LIKE

AND operator in SQL:

The AND operator is a logical operator that displays the results when all the conditions are satisfied. Two or more conditions are separated by the AND operator.

Table for AND operator :

A B Result
1 1 1
1 0 0
0 1 0
0 0 0

Syntax of AND operator :

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

The example of AND operator :

SELECT first_name, last_name, salary, age
FROM persons_table
WHERE salary < 50000 AND age < 40;

In this query, when the salary is less than 50000 and the age is less than 40, those data are retrieved.
and-operator-sql

The ALL operator returns true when all of the subquery values satisfy the condition. The ALL operator is only used in the WHERE statement. ALL operator is the sequence of the AND operator.

Syntax of the ALL operator :

SELECT column_names
FROM table_name
WHERE column_name operator ALL (SELECT column_name 
                                FROM table_name);

Example of the ALL operator :

SELECT first_name, age, salary
FROM persons_table
WHERE salary <= ALL (SELECT salary 
                     FROM persons_table);                

In this query, all the salaries in the database are checked and filtered to get all the lowest salary in the user-defined table.
all-operator-sql

Example of the sequence of the ALL operator :

SELECT first_name, age, salary
FROM persons_table
WHERE age < 29 AND salary < 50000 ;

The AND operator is the sequence of the ALL operator. In this query, the same output is retrieved as the previous query. This states that ALL operator reduses the usage of the sequence of the AND operator.
sql-and-operator

OR operator in SQL:

The OR operator is used to combine the 2 or more boolean expressions and returns true when at least one expression is true.

Table for OR operator :

A B Result
1 1 1
1 0 1
0 1 1
0 0 0

Syntax of the OR operator :

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

Example of the OR operator :

SELECT first_name, last_name, salary, age
FROM persons_table
WHERE   age < 40 OR salary < 30000;

In this query, the 2 conditions are present. If at least one condition is satisfied then the data is retrieved.
or-operator-sql

The OR operator is the sequence of the A operator. In this query, the same output is retrieved as the previous query. This states that ALL operator reduses the usage of the sequence of the AND operator.

ANY operator in SQL:

ANY operator compares a value to each value in a list of results from a query and evaluates to true if the result of an inner query contains at least one row which satisfies the given condition. It must be preceded by =, !=, >, <, <=, >=, and followed by a list or subquery. ANY operator is the sequence of the OR operator.

Syntax of the ANY operator :

SELECT column_name
FROM table_name
WHERE column_name operator ANY value;

Example of the ANY operator:

SELECT first_name, last_name, salary, age
FROM persons_table
WHERE salary < ANY 50000;

any-operator-function-sql

Example of the sequence of the ANY operator :

SELECT first_name, salary, age
FROM persons_table
WHERE   age < 40 OR salary < 30000;

The OR operator is the sequence of the ANY operator. In this query, the same output is retrieved as the previous query. This states that ANY operator reduces the usage of the sequence of the OR operator.
sql-and-operator
sql-or-operator

NOT operator in SQL:

The NOT operator retrieves the data when there is no matching record is found.

Table for NOT operator :

A NOT A
0 1
1 0

Syntax of the NOT operator :

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

Example of the NOT operator :

SELECT first_name, age, salary, city
FROM persons_table
WHERE NOT city='Sakleshpur';

In this query, the data are retrieved where the 'Sakleshpur' is not belonging to the query.
not-operator-sql

BETWEEN operator in SQL:

The BETWEEN operator selects the rows whose values are in a specified range.

Syntax of the BETWEEN operator :

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Example of the BETWEEN operator :

SELECT first_name, age, salary
FROM persons_table
WHERE age BETWEEN 20 AND 30; 

In this query, the first_name, age, and salary are selected. Where age lies between 20 and 30.
between-operator-sql

IN operator in SQL:

The IN operator is used to determine whether any given value matches the value in a subquery. The IN operator reduces the multiple uses of the OR operators in SELECT, INSERT, UPDATE, or DELETE statement.

Syntax of the IN operator :

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

Example of the IN operator :

SELECT first_name, age, salary
FROM persons_table
WHERE salary IN (20000, 30000);

In this query, the 20000 and 30000 which is present in the salary values are retrieved.
in-operator-sql

EXISTS operator in SQL:

The EXISTS operator is used to test for the existence of the rows. The operator returns true if the subquery returns which satisfies the conditions.

Syntax of the EXISTS operator :

SELECT column_name
FROM table_name
WHERE EXISTS (SELECT column_name 
              FROM table_name 
              WHERE condition);

Example of the EXISTS operator :

SELECT first_name, age, salary
FROM persons_table
WHERE EXISTS (SELECT salary 
              FROM persons_table 
              WHERE age<30);

In this query, the data is retrieved according to the given condition. The subquery returns at least one record in its result set, the EXISTS clause will evaluate to true and the EXISTS condition will be met.
exist-operator-sql

Difference between IN operator and EXISTS operator:

EXISTS

IN

The values cannot be compared between the sub-query and parent query.

The values can be compared in between sub-query and parent queries.

The output of EXISTS can be either FALSE or TRUE.

The output of IN can be TRUE or NULL or FALSE.

EXISTS is used to determine if any values are returned or not.

IN is used as a multiple OR operator.

The EXISTS is faster than IN when the sub-query result is large.

The IN is faster than EXISTS when the sub-query result is less.

LIKE operator in SQL:

The LIKE operator is used to test whether values in a column match a specified pattern. There are two wildcards often used in conjunction with the LIKE operator:

  • %: The percent sign is used to represent zero, one, or multiple characters.
  • _: The underscore sign is used to represent a single character.

Syntax of the LIKE operator :

SELECT column_name_1, column_name_2, ...
FROM table_name
WHERE column_name LIKE pattern;

Example of the LIKE operator(%) :

SELECT first_name, last_name
FROM persons_table
WHERE first_name LIKE 'Ra%';

In this query, the data is retrieved where the first_name contains the starting letters 'Ra'.
like-operator-sql

Example of the LIKE operator(_) :

SELECT first_name, last_name 
FROM persons_table
WHERE first_name LIKE 'S___';

In this query, the data is retrieved where the first_name contains 'S' as the first letter and followed by the 3 other characters.
like-oprator_-sql

The bitwise operator in SQL

Bitwise operators perform bit manipulations between two expressions of integer data types category. The BITWISE operators are used for manipulating data at the bit level, hence it is also called as bit-level programming.

The commonly used Bitwise operator are as follows,

  • Bitwise AND
  • Bitwise OR

BITWISE performs the below steps.

  • Converts the input value into binary.
  • Performs a bitwise operation on the two strings.
  • Converts the binary result back into decimal and returns the value.

The BITAND is an inbuilt function that is used to returns an integer value which is calculated with AND operation of two given input decimal numbers. BITAND is represented by a single ampersand sign (&). Two integer expressions are written on each side of the (&) operator.

Table for BITAND operator :

A B Result
1 1 1
1 0 0
0 1 0
0 0 0

Syntax for BITAND operator :

BITAND(num1, num2)

Parameters (num1, num2): This function is accepting two parameters which are num1 and num2. These two parameters are input decimal numbers which get converted into binary numbers internally and on which BITAND function is called.

SELECT BITAND(5,9)
FROM dual;

In this query, 2 decimal numbers are used they are 5 and 9. The binary number of 5 is 0101. And the binary number of 9 is 1001. The result of the bitwise AND operation returns 1 if both the bits have the value as 1; otherwise, the result is always 0.

parameter 1-      5: 0 1 0 1
parameter 2-      9: 1 0 0 1
----------------------------------
result-           1: 0 0 0 1 
----------------------------------

bitand-sql

BITOR operator in SQL:

The BITOR operator returns the result of a bitwise inclusive OR operation performed on two numeric values. BITOR is represented by a single vertical bar sign (|). Two integer expressions are written on each side of the (|) operator.

Table for BITOR operator :

A B Result
1 1 1
1 0 1
0 1 1
0 0 0

Syntax of the BITOR operator :

SELECT SUM(num1+num2-BITAND(num1,num2)) BITOR
FROM dual; 

Example of the BITOR operator :

SELECT SUM(8+10-BITAND(8,10)) BITOR
FROM dual; 

In this query, 2 decimal numbers are used they are 8 and 10. The binary number of 8 is 1000. And the binary number of 10 is 1010. The result of the bitwise OR operation returns 1 if at least one bit have the value as 1. otherwise, the result is always 0.

parameter 1-      8: 1 0 0 0
parameter 2-     10: 1 0 1 0
----------------------------------
result-          10: 1 0 1 0 
----------------------------------

sql-bitor-operator

0 results
Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions