Comparison Operators
In the daily use of data warehouses, users often need to filter records that meet specific conditions from massive amounts of data. For example, users may need to find employees whose salaries are higher than 5,000 in the employee table, filter valid orders whose amounts are not equal to 0 in the order table, or query products whose inventory is less than the security threshold in the product table. These scenarios all require the use of comparison operators.
Comparison operators are the most basic and commonly used tools for condition judgment in SQL queries. They are widely used in the following typical scenarios:
- Data filtering: In the WHERE clause of a SELECT statement, comparison operators are used to filter records that meet specific conditions. For example, SELECT * FROM employees WHERE salary > 5000.
- Combination of multiple conditions: In the WHERE clause, logical operators such as AND and OR are used together with multiple comparison operators to construct compound filter conditions.
- Data update and deletion: In the UPDATE and DELETE statements, comparison operators are used to accurately locate the rows to be operated.
- Join condition: In the ON clause of a JOIN statement, comparison operators are used to specify the conditions for joining tables.
- Subquery judgment: In a subquery, comparison operators are used to determine the existence or compare values.
Understanding the usage of comparison operators, data type conversion rules, and special processing of NULL values helps you write efficient and accurate SQL query statements.
Principles of Comparison Operators
A comparison operator is used to compare two expressions and return a Boolean result (TRUE, FALSE, or NULL). All comparison operators are binary operators, that is, they require two operands to participate in the operation.
The two data types to be compared must be the same data type or types that can be implicitly converted. For example, the expression 1 < 2 < 3 is invalid because the result of 1 < 2 is a Boolean value, and a Boolean value cannot be compared with 3.
For the comparison operators provided by DWS, see Table 1.
| Operators | Description | Syntax Example |
|---|---|---|
| < | Less than | Query employees whose salaries are lower than 5,000: SELECT * FROM employees WHERE salary < 5000; |
| > | Greater than | Query employees whose salaries are higher than 5,000: SELECT * FROM employees WHERE salary > 5000; |
| <= | Less than or equal to | Query employees whose salary is less than or equal to 5,000: SELECT * FROM employees WHERE salary <= 5000; |
| >= | Greater than or equal to | Query employees whose salary is greater than or equal to 5,000: SELECT * FROM employees WHERE salary >= 5000; |
| = | Equality | Query employees whose department ID is 10: SELECT * FROM employees WHERE department_id = 10; |
| <> or != | Inequality | Query employees whose department ID is not 10: SELECT * FROM employees WHERE department_id <> 10; SELECT * FROM employees WHERE department_id != 10; |
NULL Value Comparison
According to the SQL standard, NULL indicates an unknown value. The result of comparing NULL with any value (including NULL itself) is NULL (that is, unknown), not TRUE or FALSE.
- The result of NULL = 1 is NULL, not FALSE.
- The result of NULL = NULL is also NULL, not TRUE.
- The result of NULL <> 1 is also NULL.
In the WHERE clause, only the rows whose comparison result is TRUE are returned. The rows whose comparison result is NULL or FALSE are not returned. Therefore, you cannot use the = or <> operator to determine whether a value is NULL. Instead, you must use the IS NULL or IS NOT NULL operator.