SQL - Operators

    In SQL we have various operators to perform operations on data, and an operator can be represented by a special symbol or by a reserved character. Operators always used along with the SQL statements and they come very handy to perform reading and writing operations. The operators in SQL are divided into 3 major categories:

    • Arithmetic operators
    • Comparison operators
    • Logical operators

    Arithmetic Operator

    Arithmetic Operators can be applied between two numeric data.

    Arithmetic Operators Description SQL Example Output
    + (Addition) Used to perform the addition operation between two numeric data. select 2+3; 
    +-----+
| 2+3 |
+-----+
|   5 |
+-----+
    - (Subtraction) Used to perform the subtraction operation select 5-2; 
    +-----+
| 5-2 |
+-----+
|   3 |
+-----+
    * (Multiplication) It multiple the two values select 2*3; 
    +-----+
| 2*3 |
+-----+
|   6 |
+-----+
    / (Division) Divide the left value by the right one select 14/3; 
    +--------+
| 14/3   |
+--------+
| 4.6667 |
+--------+
    % (Modulus) Show the remainder after dividing the left value by the right one select 9%3; 
    +------+
| 9%3  |
+------+
|    0 |
+------+

    Comparison Operator

    Comparison operator compares two values and shows output in 0 and 1, here 0 represent False and 1 represent True.

    Comparison Operators Description SQL Example Output
    = (Equal To) It used between two variables, and it checks whether both the values match or Not select 3=3; 
    +-----+
| 3=3 |
+-----+
|   1 |
+-----+
    != (not Equal to ) It returns true if the values are not equal, else it returns False select 3!=3; 
    +------+
| 3!=3 |
+------+
|    0 |
+------+
    <> It is similar to not Equal to select 3<>3; 
    +------+
| 3<>3 |
+------+
|    0 |
+------+
    > (Greater than) It checks if the left value is greater than the right one. select 3>4; 
    +-----+
| 3>4 |
+-----+
|   0 |
+-----+
    < (Smaller than) It checks if the left value is smaller than the right one. select 3<4; 
    +-----+
| 3<4 |
+-----+
|   1 |
+-----+
    >= (Greater than Equal to) If the left operand is greater than or equal to the right operand then the result will be true (1) else it will be false(0) select 3>=4; 
    +------+
| 3>=4 |
+------+
|    0 |
+------+
    <=(Less than equal to) If the left operand is Smaller than or equal to the right operand then the result will be true (1) else it will be false(0) select 3<=4; 
    +------+
| 3<=4 |
+------+
|    1 |
+------+

    Logical Operators

    In SQL Logical operators represented by reserved Keywords.

    Operators Description SQL Syntax
    ALL It is used to compare a value with all the values present in another set. 
    SELECT * FROM Students 
WHERE age > ALL (SELECT age FROM Student WHERE marks >753 ) ;
    AND This logical operator used with WHERE clause and operate between two conditional operators. 
    SELECT * FROM STUDENTS WHERE age>16 AND age<19;
    ANY It shows all the result if any value in the list stands on the conditions. 
    SELECT * FROM Students 
WHERE age > ANY (SELECT age FROM Student WHERE marks >750 ) ;
    BETWEEN This operator is used to search values by providing a range. 
    SELECT * FROM STUDENTS WHERE age BETWEEN 16 AND 19;
    EXISTS It checks whether the data already exists or not, it can also be used to check if the table has been created or not. 
    SELECT age FROM Students 
WHERE EXISTS (SELECT age FROM Student WHERE marks >753 ) ;
    IN This operator checks if a value is present in a list or in a specific data set 
    SELECT * FROM STUDENTS WHERE age IN (16,17,18,19);
    LIKE It is used to compare a value with similar value and often use to compare and search data values. 
    SELECT Name FROM STUDENTS WHERE Name LIKE 'An%';
    NOT It reverses all the operators result. 
    SELECT Name FROM STUDENTS WHERE Name NOT LIKE 'An%';
    OR It shows result if any of the two operators have a true value 
    SELECT * FROM STUDENTS WHERE age>16 OR age<19;
    IS NULL Used to check if the value is Null or not 
    SELECT * FROM Students WHERE age IS NOT NULL;
    UNIQUE It is a Constraints which is used to specify the uniqueness in the table

    Summary

    • Operators used to perform specific Operations.
    • An Operator can be represented by a special symbol or by a reserved keyword.
    • In SQL we have 3 major types of Operators, Logical, Comparison and Arithmetic Operators.
    • Specific Operators can only apply on specific Data types.

    People are also reading:

    Previous Tutorial Next Tutorial