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.
- Arithmetic Operators
- Comparison Operators
- Logical Operators
- 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.
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
-
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: |
|
| <> (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: |
|
| > (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: |
|
| <=(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: |
|
| !< (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: |
|
| !> (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: – |
|
| 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: |
|
| LIKE | LIKE operator is used to compare a value to similar values using wildcard operators. | SELECT EmpName FROM Employee WHERE EmpName LIKE ‘Jo%’;
Output: |
|
| 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: |
|
| 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: |
|
| 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: |
|
| Operator | Description | ||
| & | Bitwise AND operator | ||
| | | Bitwise OR operator | ||
| ^ | Bitwise Exclusive OR operator | ||
| << | Left Shift operator | ||
| >> | Right Shift operator |