Comparison operators are used to test single values in a SQL statement. The comparison operators discussed consist of =, <>, <, and >. These operators are used to test -
Equality -
Non-equality -
Less-than values -
Greater-than values Examples and the meanings of comparison operators are covered in the following sections. Equality The equal operator compares single values to one another in a SQL statement. The equal sign (=) symbolizes equality. When testing for equality, the compared values must match exactly or no data is returned. If two values are equal during a comparison for equality, the returned value for the comparison is TRUE; the returned value is FALSE if equality is not found. This Boolean value (TRUE/FALSE) is used to determine whether data is returned according to the condition. The = operator can be used by itself or combined with other operators. An example and the meaning of the equality operator follows : Example | Meaning | WHERE SALARY = '20000' | Salary equals 20000 | The following query returns all rows of data where the PROD_ID is equal to 2345: SELECT * FROM PRODUCTS_TBL WHERE PROD_ID = '2345'; PROD_ID PROD_DESC COST ---------- ------------------------------ ------ 2345 OAK BOOKSHELF 59.99 1 row selected. Non-Equality For every equality, there are multiple non-equalities. In SQL, the operator used to measure non-equality is <> (the less-than sign combined with the greater-than sign). The condition returns TRUE if the condition finds non-equality; FALSE is returned if equality is found. | Another option comparable to <> is !=. Many of the major implementations have adopted != to represent not-equal. Check your particular implementation for the usage. | Example | Meaning | WHERE SALARY <> '20000' | Salary does not equal 20000 | SELECT * FROM PRODUCTS_TBL WHERE PROD_ID <> '2345'; PROD_ID PROD_DESC COST ---------- ------------------------------ ------ 11235 WITCHES COSTUME 29.99 222 PLASTIC PUMPKIN 18 INCH 7.75 13 FALSE PARAFFIN TEETH 1.1 90 LIGHTED LANTERNS 14.5 15 ASSORTED COSTUMES 10 9 CANDY CORN 1.35 6 PUMPKIN CANDY 1.45 87 PLASTIC SPIDERS 1.05 119 ASSORTED MASKS 4.95 1234 KEY CHAIN 5.95 2345 OAK BOOKSHELF 59.99 11 rows selected. Less-Than, Greater-Than The symbols < (less-than) and > (greater-than) can be used by themselves , or in combination with each other or other operators. Example | Meaning | WHERE SALARY < '20000' | Salary is less than 20000 | WHERE SALARY > '20000' | Salary is greater than 20000 | In the first example, anything less-than and not equal to 20000 returns TRUE. Any value of 20000 or more returns FALSE. Greater-than works the opposite of less-than. SELECT * FROM PRODUCTS_TBL WHERE COST > 20; PROD_ID PROD_DESC COST ---------- ------------------------------ ------ 11235 WITCHES COSTUME 29.99 2345 OAK BOOKSHELF 59.99 2 rows selected. In the next example, notice that the value 24.99 was not included in the query's result set. The less-than operator is not inclusive. SELECT * FROM PRODUCTS_TBL WHERE COST < 24.99; PROD_ID PROD_DESC COST ---------- ------------------------------ ------ 222 PLASTIC PUMPKIN 18 INCH 7.75 13 FALSE PARAFFIN TEETH 1.1 90 LIGHTED LANTERNS 14.5 15 ASSORTED COSTUMES 10 9 CANDY CORN 1.35 6 PUMPKIN CANDY 1.45 87 PLASTIC SPIDERS 1.05 119 ASSORTED MASKS 4.95 1234 KEY CHAIN 5.95 9 rows selected. Combination Examples of Comparison Operators The equal operator can be combined with the less-than and greater-than operators, as in the following examples: Example | Meaning | WHERE SALARY <= '20000' | Salary less-than or equal-to | WHERE SALARY >= '20000' | Salary greater-than or equal-to | Less-than or equal-to 20000 includes 20000 and all values less than 20000. Any value in that range returns TRUE; any value greater than 20000 returns FALSE. Greater-than or equal-to also includes the value 20000 in this case and works the same as the less-than or equal-to. SELECT * FROM PRODUCTS_TBL WHERE COST <= 24.99; PROD_ID PROD_DESC COST ---------- ------------------------------ ------ 222 PLASTIC PUMPKIN 18 INCH 7.75 13 FALSE PARAFFIN TEETH 1.1 90 LIGHTED LANTERNS 14.5 15 ASSORTED COSTUMES 10 9 CANDY CORN 1.35 6 PUMPKIN CANDY 1.45 87 PLASTIC SPIDERS 1.05 119 ASSORTED MASKS 4.95 1234 KEY CHAIN 5.95 9 rows selected. |