MariaDB Crash Course - Ben Forta [19]
Table 6.1 WHERE Clause Operators
Checking Against a Single Value
We have already seen an example of testing for equality. Here’s one more:
Input
SELECT prod_name, prod_price
FROM products
WHERE prod_name = 'fuses';
Output
+-----------+------------+
| prod_name | prod_price |
+-----------+------------+
| Fuses | 3.42 |
+-----------+------------+
Analysis
Checking for WHERE prod_name = 'fuses' returned a single row with a value of Fuses. By default, MariaDB is not case sensitive when performing matches, and so fuses and Fuses match.
Now look at a few examples to demonstrate the use of other operators.
This first example lists all products that cost less than 10:
Input
SELECT prod_name, prod_price
FROM products
WHERE prod_price < 10;
Output
+---------------+------------+
| prod_name | prod_price |
+---------------+------------+
| .5 ton anvil | 5.99 |
| 1 ton anvil | 9.99 |
| Carrots | 2.50 |
| Fuses | 3.42 |
| Oil can | 8.99 |
| Sling | 4.49 |
| TNT (1 stick) | 2.50 |
+---------------+------------+
This next statement retrieves all products costing 10 or less (resulting in two additional matches):
Input
SELECT prod_name, prod_price
FROM products
WHERE prod_price <= 10;
Output
+----------------+------------+
| prod_name | prod_price |
+----------------+------------+
| .5 ton anvil | 5.99 |
| 1 ton anvil | 9.99 |
| Bird seed | 10.00 |
| Carrots | 2.50 |
| Fuses | 3.42 |
| Oil can | 8.99 |
| Sling | 4.49 |
| TNT (1 stick) | 2.50 |
| TNT (5 sticks) | 10.00 |
+----------------+------------+
Checking for Nonmatches
This next example lists all products not made by vendor 1003:
Input
SELECT vend_id, prod_name
FROM products
WHERE vend_id <> 1003;
Output
+---------+--------------+
| vend_id | prod_name |
+---------+--------------+
| 1001 | .5 ton anvil |
| 1001 | 1 ton anvil |
| 1001 | 2 ton anvil |
| 1002 | Fuses |
| 1005 | JetPack 1000 |
| 1005 | JetPack 2000 |
| 1002 | Oil can |
+---------+--------------+
* * *
Tip: When to Use Quotes
If you look closely at the conditions used in the examples’ WHERE clauses, you will notice that some values are enclosed within single quotes (such as 'fuses' used previously), and others are not. The single quotes are used to delimit strings. If you are comparing a value against a column that is a string datatype, the delimiting quotes are required. Quotes are not used to delimit values used with numeric columns.
* * *
The following is the same example, except this one uses the != operator instead of <>:
Input
SELECT vend_id, prod_name
FROM products
WHERE vend_id != 1003;
* * *
Note: != Versus <>
Yes, both <> and != look for nonmatches. != means not equal to, and <> means less than or greater than (in other words, not equal to). Use whichever you prefer.
* * *
Checking for a Range of Values
To check for a range of values, you can use the BETWEEN operator. Its syntax is a little different from other WHERE clause operators because it requires two values: the beginning and end of the range. The BETWEEN operator can be used, for example, to check for all products that cost between 5 and 10 or for all dates that fall between specified start and end dates.
The following example demonstrates the use of the BETWEEN operator by retrieving all products with a price between 5 and 10:
Input
SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;
Output
+----------------+------------+
| prod_name | prod_price |
+----------------+------------+
| .5 ton anvil | 5.99 |
| 1 ton anvil | 9.99 |
| Bird seed | 10.00 |
| Oil can | 8.99 |
| TNT (5 sticks) | 10.00 |
+----------------+------------+
Analysis
As seen in this example, when BETWEEN is used, two values must be specified—the low end and high end of the desired range. The two values must also be separated by the AND keyword. BETWEEN matches all the values in the range, including the specified range start and end values.
Checking for