Restricting Data Using the WHERE Clause

The WHERE clause uses comparison operators to identify specific rows in a table. When used with the SELECT statement, you can selectively retrieve rows from a table, rather than retrieving all rows of a table.

Comparison operators include those listed in Table: Comparison Operators.

Comparison Operators

Operator	Definition
=, !=, <>	Test for equal to, not equal to, not equal to
>, >=, <, <=	Test for greater than, greater than or equal to, less than, less than or equal to
BETWEEN ... AND ...	Checks for a range between and including two values
LIKE	Searches for a match in a string, using the wildcard symbols % (zero or multiple characters) or _ (one character)
IN ( ), NOT IN ( )	Tests for a match, or not match, in a specified list of values
IS NULL, IS NOT NULL	Checks whether a value is null, is not null

Example: Selecting Data With the SQL WHERE Clause to Restrict Data shows how to use SELECT with a WHERE clause and several comparison operators to retrieve specific rows of data from the employees table.

Selecting Data With the SQL WHERE Clause to Restrict Data

-- the following retrieves data where the manager_id equals 122
SELECT * FROM employees WHERE manager_id = 122;

-- this retrieves data where the manager_id equals 122 and job_id is ST_CLERK
SELECT * FROM employees WHERE manager_id = 122 AND job_id = 'ST_CLERK';

-- this retrieves employees with managers with IDs between 122 and 125 inclusive
SELECT * FROM employees WHERE manager_id BETWEEN 122 AND 125;

-- this uses LIKE with the wildcard % to retrieve employee data 
-- where the last name contains mar somewhere in the name string
SELECT employee_id, last_name FROM employees WHERE last_name LIKE '%mar%';

-- this uses LIKE with the wildcard % to retrieve employee data 
-- from the employees table where the last name starts with Mar
SELECT employee_id, last_name FROM employees WHERE last_name LIKE 'Mar%';

-- this retrieves employee data where the commission percentage is not null
SELECT employee_id, last_name FROM employees WHERE commission_pct IS NOT NULL;

-- the following retrieves data where the employee_id equals 125, 130, or 135
SELECT employee_id, last_name, first_name FROM employees
       WHERE employee_id IN (125, 130, 135);

Using Character Literals in SQL Statements

Many SQL statements contain conditions, expressions, and functions that require you to specify character literal values. By default, you must use single quotation marks with character literals, such as 'ST_CLERK' or 'Mar%'. This technique can sometimes be inconvenient if the text itself contains single quotation marks. In such cases, you can also use the quote-delimiter mechanism, which enables you to specify q or Q followed by a single quotation mark and then another character to be used as the quotation mark delimiter.

The quote-delimiter can be any single-byte or multi-byte character except for a space, tab, or return. If the opening quote-delimiter is a left bracket [, left brace {, left angle bracket <, or left parenthesis ( character, then the closing quote delimiter must be the corresponding right bracket ], right brace }, right angle bracket >, or right parenthesis ) character. In all other cases, the opening and closing delimiter must be identical.

The following character literals use the alternative quoting mechanism:

q'(name LIKE '%DBMS_%%')'
q'#it's the "final" deadline#'
q'<'Data,' he said, 'Make it so.'>'
q'"name like '['"'

You can specify national character literals for unicode strings with the N'text' or n'text' notation, where N or n specifies the literal using the national character set. For example, N'résumé' is a national character literal. For information about unicode literals, see "Unicode String Literals".