SQL Operators

Filed Under: SQL

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

SQL Operators

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.

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

SQL Logical Operators

Logical 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.

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

SQL Bitwise Operators

Bitwise operators are the operators which are used on bit of data.

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

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.

Comments

  1. Valentino says:

    And finally,

    select 27 ^ 19′

    is also wrong, it should return 01000.

    Thanks for the effort, but I think you should just review this guide, there are too many wrong points,.

  2. Valentino says:

    The ANY is also incorrect, the returned list should be John, Henry., a

    The EXISTS is wrong, the way you are using it returns all the 3 names, because the EXISTS is true, and you are just selecting all the table rows, it is equivalent to doing:

    SELECT EmpName FROM Employee
    WHERE 1=1;

    I guess, the way you wanted to use EXISTS was:

    SELECT a.EmpName FROM Employee a WHERE EXISTS (SELECT b.EmpName FROM Employee b WHERE b.EmpSalary >= 2500 AND a.EmpName=b.EmpName)

    The use of UNIQUE is also not convincing. Normally UNIQUE is used when creating a table to establish a constraint on a column, I guess you here meant to use DISTINCT in place of UNIQUE.

  3. Valentino says:

    The output of operator ALL is also incorrect, it should be only John.

  4. Valentino says:

    Pankaj, I have never used the (Not Less than) operator, but for the (Not Less than) operator you are selecting the rows for which EmpSalary is greater or equal than 2000, So, I expect also John to be in the list, Or am I misunderstanding? Thanks for the tutorial!

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages