In This Article, You Will Learn About SQL Operators.
SQL Operators – Before moving ahead, let’s know a bit about the SQL Comments.
Table of Contents
SQL Operators
The SQL operators are defined as operators like Arithmetic, Comparison, , and Compound which are used to perform a specific action based on operators.
SQL Arithmetic Operators
The SQL Arithmetic operators are those which include mathematical operators such as addition, subtraction, multiplication, and division etc.
Table: 1
Operator | Name | Description |
+ | Add | Use to add two or more values |
– | Subtract | Use to subtract two or more values |
* | Multiply | Use to multiply two or more values |
/ | Divide | Use to divide two or more values |
% | Modulo | Use to return remainder |
Example: Use the Add ( + ) operator to add values.
SELECT 10 + 20;
Example: Use the Subtract ( – ) operator to subtract values.
SELECT 10 - 20;
Example: Use the Multiply ( * ) operator to multiply values.
SELECT 10 * 20;
Example: Use the Divide ( / ) operator to divide values.
SELECT 10 / 20;
Example: Use the Modulo ( % ) operator to multiply values.
SELECT 11 % 2;
SQL Bitwise Operators
Operator | Name |
& | Bitwise AND |
| | Bitwise OR |
^ | Bitwise exclusive OR |
SQL Comparison Operators
The SQL Comparison operators are used to compare two numerical values.
Operators | Name | Description |
= | Equal to | Check whether one value is equal to other value |
> | Greater than | Check whether one value is greater than other value |
< | Less than | Check whether one value is less than other value |
>= | Greater than or equal to | Check whether one value is greater than or equal to other value |
<= | Less than or equal to | Check whether one value is less than or equal to other value |
<> | Not equal to | Check whether one value is not equal to other value |
Table: Numbers
Name | Marks |
A | 10 |
B | 20 |
C | 30 |
D | 40 |
E | 50 |
Example: Use the Equal ( = ) comparison to compare two values.
SELECT * FROM Numbers
WHERE Marks = 20;
Example: Use the Greater than ( > ) comparison to compare two values.
SELECT * FROM Numbers
WHERE Marks > 20;
Example: Use the Less than ( < ) comparison to compare two values.
SELECT * FROM Numbers
WHERE Marks < 20;
Example: Use the Greater than or Equal To ( >= ) comparison to compare two values.
SELECT * FROM Numbers
WHERE Marks >= 20;
Example: Use the Less than or Equal To ( <= ) comparison to compare two values.
SELECT * FROM Numbers
WHERE Marks <= 20;
Example: Use the Not Equal To ( <= ) comparison to compare two values.
SQL Compound Operators
Name | Operators |
+= | Add equals |
-= | Subtract equals |
*= | Multiply equals |
/= | Divide equals |
%= | Modulo equals |
&= | Bitwise AND equals |
^-= | Bitwise exclusive equals |
|*= | Bitwise OR equals |
SQL Logical Operators
Operator | Description |
ALL | TRUE if all of the subquery values meet the condition |
AND | TRUE if all the conditions separated by AND is TRUE |
ANY | TRUE if any of the subquery values meet the condition |
BETWEEN | TRUE if the operand is within the range of comparisons |
EXISTS | TRUE if the subquery returns one or more records |
IN | TRUE if the operand is equal to one of a list of expressions |
LIKE | TRUE if the operand matches a pattern |
NOT | Displays a record if the condition(s) is NOT TRUE |
OR | TRUE if any of the conditions separated by OR is TRUE |
SOME | TRUE if any of the subquery values meet the condition |
Example: Use the ALL operator to check the condition.
SELECT Name
FROM Numbers
WHERE Marks = ALL (SELECT Marks FROM Name WHERE Marks = 20);
Example: Use the AND operator to check the condition.
SELECT * FROM Numbers
WHERE Name = 'A' AND Marks = 20;
Example: Use the ANY operator to check the condition.
SELECT * FROM Numbers
WHERE Marks < ANY (SELECT Marks FROM Numbers);
Example: Use the BETWEEN operator to check the condition.
SELECT * FROM Numbers
WHERE Marks BETWEEN 10 AND 40;
Example: Use the EXISTS operator to check the condition.
SELECT Name
FROM Data_1
WHERE EXISTS (SELECT Class FROM Data_1 WHERE Data_1.S.No = Data_2.S.No);
NOTE: Refer SQL EXISTS for more details.
Example: Use the IN operator to check the condition.
SELECT * FROM Number
WHERE Marks IN (20, 30);
Example: Use the LIKE operator to check the condition.
SELECT * FROM Number
WHERE Name LIKE 'L%';
Example: Use the NOT operator to check the condition.
SELECT * FROM Number
WHERE Name NOT LIKE 'A%';
Example: Use the OR operator to check the condition.
SELECT * FROM Number
WHERE Name = 'A' OR Marks = 70;
Example: Use the SOME operator to check the condition.
SELECT * FROM Number
WHERE Marks < SOME (SELECT * FROM Number WHERE Marks < 30);
If you find anything incorrect in the above-discussed topic and have further questions, please comment below.
Connect on:
