SQL Operators
An operator is a symbol that specifies an action to perform within a clause of an SQL statement, for example, the ‘+’ symbol in the expression ‘1 + 2’ would add the two numbers together to produce a result. This type of operator is known as an arithmetic operator, however, there are also other groups of operators within SQL that are explained below.
Arithmetic Operators
Arithmetic operators allow for mathematical calculations to be carried out within a clause of an SQL statement.
| Operator | Description |
|---|---|
| + | Adds two values together. |
| – | Subtracts one value from another. |
| * | Multiplies two values together. |
| / | Divides one value by another. |
| % | Calculates the remainder after one value is divided by another. |
Relational Operators
Relational operators compare one value with another value, or set of values, and are used, for example, in the ‘where’ clause of an SQL statement to restrict the data that is returned. The result of the comparison is either True or False.
| Operator | Description |
|---|---|
| = | Evaluates to True if the two values are equal. |
| != | Evaluates to True if the two values are not equal. |
| <> | Evaluates to True if the two values are not equal. Similar to the above. |
| > | Evaluates to True if the first value is greater than the second. |
| < | Evaluates to True if the first value is less than the second. |
| >= | Evaluates to True if the first value is greater than or equal to the second. |
| <= | Evaluates to True if the first value is less than or equal to the second. |
| !> | Evaluates to True if the first value is not greater than the second. |
| !< | Evaluates to True if the first value is not less than the second. |
| IN | Evaluates to True if the first value exists in a set of values. |
| BETWEEN | Evaluates to True if the first value is between two other values. |
| LIKE | Can be used for partial comparisons of values, for example, evaluates to True if the first value begins with the second. |
Logical Operators
Logical operators are used where you want to test more than one condition at the same time, for example, in the ‘where’ clause of an SQL statement.
| Operator | Description |
|---|---|
| AND | Logical AND operator where operands on both sides must be True to evaluate to True. |
| OR | Logical OR operator where only one of the two operands must be True to evaluate to True. |
| NOT | Logical NOT operator that can be used in conjunction with ‘AND’ or ‘OR’, along with other operators, to reverse the resulting operand. In the case of ‘AND’, if both conditions evaluate to True, then with the use of the ‘NOT’ operator the result would be False. |
Set Operators
Set operators combine the results of two SQL statements into one results set.
| Operator | Description |
|---|---|
| UNION | Returns all distinct rows in both queries. |
| UNION ALL | Returns all rows from both queries, including duplicates. |
| INTERSECT | Returns all rows in the first query that are identical to those in the second. |
| MINUS | Returns all rows in the first query that are not returned in the second. |