Home arrow Oracle arrow Page 4 - Mastering the WHERE Clause

Conditions and Expressions - Oracle

Gain the full power of SQL to write queries in an Oracle environment with this updated book (new information on Oracle 10g). This chapter focuses on the role of the WHERE clause in SQL statements and the various options available when building a WHERE clause. (Mastering Oracle SQL by Sanjay Mishra and Alan Beaulieu, O'Reilly, ISBN: 596006322.)

  1. Mastering the WHERE Clause
  2. WHERE to the Rescue
  3. WHERE Clause Evaluation
  4. Conditions and Expressions
  5. Membership Conditions and Range Conditions
  6. Matching Conditions
  7. Regular Expressions and Handling NULL
  8. Placement of Join Conditions
By: O'Reilly Media
Rating: starstarstarstarstar / 22
October 19, 2004

print this article



Now that you understand how conditions are grouped together and evaluated, it’s time to take a look at the different elements that make up a condition. A condition is comprised of one or more expressions along with one or more operators. Examples of expressions include:

  • Numbers

  • Columns, such as s.supplier_id

  • Literals, such as ‘Acme Industries’

  • Functions, such as UPPER(‘abcd’)

  • Lists of simple expressions, such as (1, 2, 3)
  • Subqueries

Examples of operators include:

  • Arithmetic operators, such as +, -, *, and /

  • Comparison operators, such as =, <, >=, !=, LIKE, and IN

The following sections explore many of the common condition types that use different combinations of the preceeding expression and operator types.

Equality/Inequality Conditions

Most of the conditions found in a WHERE clause will be equality conditions used to join data sets together or to isolate specific values. You have already encountered these types of conditions numerous times in previous examples, including:

s.supplier_id = p.supplier_id

s.name = 'Acme Industries'

supplier_id = (SELECT supplier_id
  FROM supplier
  WHERE name = 'Acme Industries')

All three conditions are comprised of a column expression followed by a comparison operator (=) followed by another expression. The conditions differ in the type of expression on the right side of the comparison operator. The first example compares one column to another, the second example compares a column to a literal, and the third example compares a column to the value returned by a subquery.

You can also build conditions that use the inequality comparison operator (!=). In a previous example, the NOT operator was used to find information about parts supplied by every supplier other than Acme Industries and Tilton Enterprises. Using the != operator rather than using NOT makes the query easier to understand and removes the need for the OR operator:

SELECT p.part_nbr, p.name, p.supplier_id, p.status, p.inventory_qty,
  s.supplier_id, s.name
FROM part p, supplier s
WHERE s.supplier_id = p.supplier_id
  AND s.name != 'Acme Industries'
  AND s.name != 'Tilton Enterprises';

While this is an improvement over the previous version, the next section shows an even cleaner way to represent the same logic.  

Buy the book!If you've enjoyed what you've seen here, or to get more information, click on the "Buy the book!" graphic. Pick up a copy today!

Visit the O'Reilly Network http://www.oreillynet.com for more online content.

>>> More Oracle Articles          >>> More By O'Reilly Media

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: