Operators in SQL
The SQL reserved words and characters are called operators, which are used with a WHERE clause in a SQL query. In SQL, an operator can either be a unary or binary operator. The unary operator uses only one operand for performing the unary operation, whereas the binary operator uses two operands for performing the binary operation.
Syntax of Unary SQL Operator
Operator SQL_Operand
Types of Operator
SQL operators are categorized in the following categories:
SQL Arithmetic Operators
SQL Comparison Operators
SQL Logical Operators
SQL Set Operators
SQL Bit-wise Operators
SQL Unary Operators
SQL Arithmetic Operators
| Operator | Description |
|---|---|
| + (Addition) | Adds values on either side of the operator. |
| - (Subtraction) | Subtracts right hand operand from left hand operand. |
| * (Multiplication) | Multiplies values on either side of the operator. |
| / (Division) | Divides left hand operand by right hand operand. |
| % (Modulus) | Divides left hand operand by right hand operand and returns remainder. |
SQL Comparison Operators
| Operator | Description |
|---|---|
| = | Checks if the values of two operands are equal or not, if yes then condition becomes true. |
| != | Checks if the values of two operands are equal or not, if values are not equal then condition becomes true. |
| <> | Checks if the values of two operands are equal or not, if values are not equal then condition becomes true. |
| > | Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true. |
| < | Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true. |
| >= | Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true. |
| <= | Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true. |
| !< | Checks if the value of left operand is not less than the value of right operand, if yes then condition becomes true. |
| !> | Checks if the value of left operand is not greater than the value of right operand, if yes then condition becomes true. |
SQL Logical Operators
| Operator | Descriptio |
|---|---|
| ALL | The ALL operator is used to compare a value to all values in another value set. |
| AND | The AND operator allows the existence of multiple conditions in an SQL statement’s WHERE clause. |
| ANY | The ANY operator is used to compare a value to any applicable value in the list as per the condition. |
| BETWEEN | The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value. |
| EXISTS | The EXISTS operator is used to search for the presence of a row in a specified table that meets a certain criterion. |
| IN | The IN operator is used to compare a value to a list of literal values that have been specified. |
| LIKE | The LIKE operator is used to compare a value to similar values using wildcard operators. |
| NOT | The NOT operator reverses the meaning of the logical operator with which it is used. Eg: NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator. |
| OR | The OR operator is used to combine multiple conditions in an SQL statement’s WHERE clause. |
| IS NULL | The NULL operator is used to compare a value with a NULL value. |
SQL Set Operators
The Set Operators in SQL combine a similar type of data from two or more SQL database tables.
It mixes the result, which is extracted from two or more SQL queries, into a single result.
Set operators combine more than one select statement in a single query and return a specific result set.
Following are the various set operators which are performed on the similar data stored in the two SQL database tables:
SQL Union Operator
SQL Union ALL Operator
SQL Intersect Operator
SQL Minus Operator
SQL Unary Operators
The Unary Operators in SQL perform the unary operations on the single data of the SQL table, i.e., these operators operate only on one operand.
These types of operators can be easily operated on the numeric data value of the SQL table.
Following are the various unary operators which are performed on the numeric data stored in the SQL table:
SQL Unary Positive Operator
SQL Unary Negative Operator
SQL Unary Bitwise NOT Operator
SQL Bitwise Operators
The Bitwise Operators in SQL perform the bit operations on the Integer values.
To understand the performance of Bitwise operators, you just knew the basics of Boolean algebra.
Following are the two important logical operators which are performed on the data stored in the SQL database tables:
Bitwise AND (&)
Bitwise OR(|)