By Melanie Caffrey
Part 4 in a series on the basics of the relational database and SQL
Part 3 in this series, “Getting Answers with SELECT Statements ” (Oracle Magazine. January/February 2012), introduced the anatomy of a SELECT statement (or query) and the importance of ascertaining which tables contain data of interest. Now that you’re familiar with a SELECT statement’s basic functionality, you can start filtering your data to limit the output in meaningful ways. The WHERE clause enables you to narrow the scope of the data a SELECT statement retrieves (or fetches ). WHERE, and its associated comparison and logical operators, are the focus of this article.
To try out the examples in this and subsequent articles in the series, you need access to an Oracle Database instance. If necessary, download and install an Oracle Database edition for your operating system. I recommend installing Oracle Database, Express Edition.
If you install the Oracle Database software, choose the installation option that enables you to create and configure a database. A new database, including sample user accounts and their associated schemas. will be created for you. (Recall from Part 1 of this series that a schema is typically a grouping of objects, such as tables, that serve a similar business function.) SQL_101 is the user account you’ll use for the examples in this article; it’s also the schema in which you will create database tables
and other objects. When the installation process prompts you to specify schema passwords, enter and confirm passwords for SYS and SYSTEM and make a note of them. Finally, whether you installed the database software from scratch or have access to an existing Oracle Database instance, download and unzip the SQL script and run it to create the example tables for the SQL_101 schema.
The SQL queries in this article are executed against tables in the SQL_101 schema with the SQL*Plus tool.
Setting Limits by Comparing
To filter the data a query retrieves, you add a WHERE clause—also called a predicate list or a set of conditions —to your SQL statement. In a nutshell, the WHERE clause specifies criteria that must be met before records are included in your query result set. The WHERE clause must specify a WHERE clause condition (or conditions) that the database software evaluates to be true or false—alternatively, the software can determine the absence of a value. A WHERE clause consists of conditional expressions. A conditional expression takes the form
<left_expression> <as compared with> <right_expression>
Here are some examples of common types of conditional expressions:
A literal character value, or string. is any list of alphanumeric characters enclosed in single quotation marks, such as ‘Smith’, ‘73abc’, or ’15-MAR-1965’.
Comparison operators compare expressions to determine the appropriate data for selection. Table 1 shows commonly used comparison operators.