Sams Teach Yourself SQL in 24 Hours

Recommended Book

Sams Teach Yourself SQL in 24 Hours, 3rd Edition

Learn More Buy

Recommended Book

Recommended Book 

Sams Teach Yourself SQL in 24 Hours, 3rd Edition

Learn More Buy

Comparison Operators

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:

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.

   

   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.

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:

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.