MariaDB Crash Course - Ben Forta [20]
When a table is created, the table designer can specify whether individual columns can contain no value. When a column contains no value, it is said to contain a NULL value.
* * *
New Term: NULL
No value, as opposed to a field containing 0, or an empty string, or just spaces.
* * *
To determine if a value is NULL, you cannot simply check to see if = NULL. Instead, the SELECT statement has a special WHERE clause that can be used to check for columns with NULL values—the IS NULL clause. The syntax looks like this:
Input
SELECT prod_name
FROM products
WHERE prod_price IS NULL;
This statement returns a list of all products that have no price (an empty prod_price field, not a price of 0), and because there are none, no data is returned. The customers table, however, does contain columns with NULL values—the cust_email column contains NULL if a customer has no e-mail address on file:
Input
SELECT cust_id
FROM customers
WHERE cust_email IS NULL;
Output
+---------+
| cust_id |
+---------+
| 10002 |
| 10005 |
+---------+
* * *
Caution: NULL and Nonmatches
You might expect that when you filter to select all rows that do not have a particular value, rows with a NULL will be returned. But they will not. Because of the special meaning of unknown, the database does not know whether they match, and so they are not returned when filtering for matches or when filtering for nonmatches.
When filtering data, make sure to verify that the rows with a NULL in the filtered column are really present in the returned data.
* * *
Summary
In this chapter, you learned how to filter returned data using the SELECT statement’s WHERE clause. You learned how to test for equality, nonequality, greater than and less than, value ranges, and NULL values.
7. Advanced Data Filtering
In this chapter, you learn how to combine WHERE clauses to create powerful and sophisticated search conditions. You also learn how to use the NOT and IN operators.
Combining WHERE Clauses
All the WHERE clauses introduced in Chapter 6, “Filtering Data,” filter data using a single criterion. For a greater degree of filter control, MariaDB allows you to specify multiple WHERE clauses. These clauses may be used in two ways: as AND clauses or as OR clauses.
* * *
New Term: Operator
A special keyword used to join or change clauses within a WHERE clause. Also known as logical operators.
* * *
Using the AND Operator
To filter by more than one column, you use the AND operator to append conditions to your WHERE clause. The following code demonstrates this:
Input
SELECT prod_id, prod_price, prod_name
FROM products
WHERE vend_id = 1003 AND prod_price <= 10;
Analysis
The preceding SQL statement retrieves the product name and price for all products made by vendor 1003 as long as the price is 10 or less. The WHERE clause in this SELECT statement is made up of two conditions, and the keyword AND is used to join them. AND instructs the DBMS to return only rows that meet all the conditions specified. If a product is made by vendor 1003 but it costs more than 10, it is not retrieved. Similarly, products that cost less than 10 that are made by a vendor other than the one specified are not retrieved. The output generated by this SQL statement is as follows:
Output
+---------+------------+----------------+
| prod_id | prod_price | prod_name |
+---------+------------+----------------+
| FB | 10.00 | Bird seed |
| FC | 2.50 | Carrots |
| SLING | 4.49 | Sling |
| TNT1 | 2.50 | TNT (1 stick) |
| TNT2 | 10.00 | TNT (5 sticks) |
+---------+------------+----------------+
* * *
New Term: AND
A keyword used in a WHERE clause to specify that only rows matching all the specified conditions should be retrieved.
* * *
The example just used contained a single AND clause and was thus made up of two filter conditions. Additional filter conditions could be used as well, each separated by an AND keyword.
* * *
Note: No ORDER BY Clause Specified
In the interests of saving space (and your typing) I omitted