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: