Database Reference
In-Depth Information
▼
Output
+-------------------------+
| vend_title |
+-------------------------+
| ACME (USA) |
| Anvils R Us (USA) |
| Furball Inc. (USA) |
| Jet Set (England) |
| Jouets Et Ours (France) |
| LT Supplies (USA) |
+-------------------------+
Views are also useful for applying common
WHERE
clauses. For example, you
might want to define a
customeremaillist
view so it filters out customers
without e-mail addresses. To do this, you can use the following statement:
▼
Input
CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;
▼
Analysis
Obviously, when sending e-mail to a mailing list you want to ignore users who
have no e-mail address. The
WHERE
clause here filters out those rows that have
NULL
values in the
cust_email
columns so they are not retrieved.
View
customeremaillist
can now be used for data retrieval just like any
table.
▼
Input
SELECT *
FROM customeremaillist;
▼
Output
+---------+----------------+---------------------+
| cust_id | cust_name | cust_email |
+---------+----------------+---------------------+
| 10001 | Coyote Inc. | ylee@coyote.com |
| 10003 | Wascals | rabbit@wascally.com |
| 10004 | Yosemite Place | sam@yosemite.com |
+---------+----------------+---------------------+