Microsoft Office Tutorials and References
In Depth Information
Both of the prior examples retrieve all the rows of data from the database. Although doing so
can be useful in many situations, it can also cause significant problems, too. Imagine that
you’re working for a large company whose tables contain millions of rows of data. Retrieving
all that data would overwhelm any computer. Instead of returning every row from a table, if
all you really want to do is to retrieve the rows associated with a particular customer or for a
particular Zip code, you can use the Where clause to pick just the rows you want.
Important If your Select statement doesn’t contain a Where clause, you should probably
ask if you really need all the data or if you just need some of the rows. Although it can be
appropriate to retrieve all the rows from a table (for example, when you want to copy a table
to a worksheet), in most cases it isn’t necessary and could have a negative impact on your
database server’s performance.
Using Simple Search Expressions
Using the Where clause entails creating an expression that identifies the rows you want to
retrieve. If the expression is Tr ue , the row will be returned; otherwise, it’ll be ignored.
For example, the following Select statement retrieves only the rows where the CustomerId is
101. Because the CustomerId column is the primary key for this table, this statement will
always return a single row from the table.
Where CustomerId = 101
Notice that you can use other comparison operators in the Where clause. In addition to the
equals sign (=), you can use the less than sign (<), the greater than sign (>), the less than or
equal to sign (<=), the greater than or equal to sign (>=), or the not equal to sign (<>).
Using Complex Search Expressions
You can also use other operators such as And , Or, and Not to create more complex expres
sions. This query chooses every customer that was added during 2003.
Where DateAdded >= ’01-Jan-2003’ And DateAdded <= ’31-Dec-2003’
Note The SQL syntax requires that non-numeric values such as character strings and
dates should be enclosed in single quotes (‘). Double quotes (“) are used to specify column
and table names which contain spaces.