SQL Operators With Examples

SQL Operators are a series of characters, symbols and words which are used as part of the WHERE clause.

SQL Operators

 

  • SQL operators are used to perform operations like comparisons and arithmetic operations.
  • These Operators are used to specify conditions in an SQL statement.
  • SQL operators help us in selecting only specific records from the tables or views.

SQL Operators Types

Broadly SQL operators are classified in following parts.

  1. Arithmetic Operators
  2. Comparison Operators
  3. Logical Operators
  4. Bitwise Operators

Let’s try to try to understand all the above-mentioned operators one by one.

SQL Arithmetic Operators

SQL Arithmetic operators are the operators which are used for mathematical calculation like addition, subtraction etc. They are used with SQL numeric data types.

Operator Description Example
+ (Addition) Adds values on both sides of the operator. SELECT 30 + 20; Output: 50
-(Substraction) Subtracts values on right side from the value on left side of the operator. SELECT 30 – 20; Output: 10
*(Multiplication) Multiplies the values on both sides of the operator SELECT 30 * 20; Output: 600
/(Division) Divides left hand side value by right hand side value. SELECT 30 / 20; Output: 1
%(Modulus) Divides left hand side value by right hand side value and returns the reminder SELECT 30 % 20; Output: 10

SQL Comparison Operators

Comparison operators are the operators which are used for comparison between two values. To understand the comparison operator better, we will take example of Employee table as shown below.

Let’s understand usage of comparison operators using the table above as an example.SQL Logical OperatorsLogical operators are the operators which are used for logical operations. To understand the logical operator better, we will take example of Employee table as shown below.
SQL Operators example
Let’s understand usage of logical operator using the table above as an example.SQL Bitwise OperatorsBitwise operators are the operators which are used on bit of data.Here is a simple program showing usage of sql bitwise operators.
-- 27 = 11011
-- 19 = 10011
select 27 & 19; -- 10011
select 27 | 19; -- 11011
select 27 ^ 19; -- 00100
select 5 << 2; -- 101 to 10100 i.e. 20
select 17 >> 2; -- 10001 to 100 i.e. 4
That’s all for SQL operators in a nutshell.

  • Prev

    SQL Interview Questions and Answers

EmpId EmpName EmpAge EmpSalary
1 John 32 2000
2 Smith 25 2500
3 Henry 29 3000
Operator Description Example
= (Equal To) Checks if the values of two operands are equal, if its equal then condition becomes true. SELECT EmpName FROM Employee WHERE EmpSalary=2000; Output: John
!= (Not Equal To) Checks if the values of two operands are not equal, if values are not equal then condition becomes true. SELECT EmpName FROM Employee WHERE EmpSalary!=2000;

Output:
Smith
Henry

<> (Not Equal To) Checks if the values of two operands are equal or not, if values are not equal then condition becomes true. SELECT EmpName FROM Employee WHERE EmpSalary<>2000;

Output:
Smith
Henry

> (Greater Than) Checks if the value of left operand is greater than the value of right operand, condition becomes true if it is yes. SELECT EmpName FROM Employee WHERE EmpSalary > 2000
Output:
Smith
Henry
< (Less Than) Checks if the value of left operand is less than the value of right operand, condition becomes true if it is yes. SELECT EmpName FROM Employee WHERE EmpSalary < 2000
Output:
No Records Found
>= (Greater than or Equal To) Checks if the value of left operand is greater than or equal to the value of right operand, condition becomes true if its yes. SELECT EmpName FROM Employee WHERE EmpSalary >= 2000

Output:
John
Smith
Henry

<=(Less than or Equal To) Checks if the value of left operand is less than or equal to the value of right operand, condition becomes true if it is yes. SELECT EmpName FROM Employee WHERE EmpSalary <= 2000

Output:
John

!< (Not Less than) Checks if the value of left operand is not less than the value of right operand, condition becomes true if it is yes. SELECT EmpName FROM Employee WHERE EmpSalary !< 2000

Output:
Smith
Henry

!> (Not Greater Than) Checks if the value of left operand is not greater than the value of right operand, condition becomes true if it is yes. SELECT EmpName FROM Employee WHERE EmpSalary !> 2000

Output: –
John

Operator Description Example
ALL ALL operator is used to compare a value to all the values in another set of values. SELECT EmpName FROM Employee
WHERE EmpAge > ALL (SELECT EmpAge FROM Employee WHERE EmpSalary >= 2500);

Output:
John
Smith

AND AND operator allows the multiple conditions in an SQL statement’s WHERE clause. SELECT EmpName FROM Employee WHERE EmpSalary > 2000 and EmpAge > 28
Output:
Henry
ANY ANY operator is used to compare a value to any applicable value in the list based on the condition. SELECT EmpName FROM Employee
WHERE EmpAge > ANY (SELECT EmpAge FROM Employee WHERE EmpSalary >= 2500);
Output:
John
Smith
BETWEEN BETWEEN operator is used to search for values that are within a range, given the minimum value and the maximum value. SELECT EmpName FROM Employee WHERE EmpAge BETWEEN 25 AND 30;
Output:
Smith
Henry
EXISTS EXISTS operator is used to search for the presence of a row in a specified table that meets a certain criterion. SELECT EmpName FROM Employee
WHERE EXISTS (SELECT EmpName FROM Employee WHERE EmpSalary >= 2500);Output:
Smith
Henry
IN IN operator is used to compare a value to a list of literal values that have been specified. SELECT EmpName FROM Employee WHERE EmpSalary IN (2000, 2500);

Output:
John
Smith

LIKE LIKE operator is used to compare a value to similar values using wildcard operators. SELECT EmpName FROM Employee WHERE EmpName LIKE ‘Jo%’;

Output:
John

NOT NOT operator reverses the meaning of the logical operator with which it is used. SELECT EmpName FROM Employee WHERE EmpSalary IS NOT NULL

Output:
John
Smith
Henry

OR OR operator is used to combine multiple conditions in one SQL statement’s WHERE clause. SELECT EmpName FROM Employee WHERE EmpSalary > 2000 OR EmpName IS NOT NULL;

Output:
John
Smith
Henry

IS NULL IS NULL operator is used to compare a value with a NULL value. SELECT EmpName FROM Employee WHERE EmpSalary IS NULL;
Output:
No records found
UNIQUE UNIQUE operator searches every row of a specified table for uniqueness SELECT UNIQUE(EmpName) FROM Employee WHERE EmpSalary IS NOT NULL;

Output:
John
Smith
Henry

Operator Description
& Bitwise AND operator
| Bitwise OR operator
^ Bitwise Exclusive OR operator
<< Left Shift operator
>> Right Shift operator

By admin

Leave a Reply