Limiting Retrieved Data—WHERE

There are some times when we want just records matching a given criteria. For instance, we want to retrieve employee records that work from a given branch only or those that earn a salary above $2000. We may also wish to get employees whose last name starts with letter K or those who earn a commission. There could be an increment in salary for only those employees who have worked for the company for 10 years and the change needs to be effected. All these are scenarios we face everyday for example, if I want to call Pamela, I will type letter P in the phonebook search bar. Behind the search bar lies a SQL statement to filter for you names matching your condition.

The WHERE clause is used to limit the data retrieved from the database. The WHERE clause comes after the table name in the SELECT statement and it is preceded with a condition. The condition is what we want the returned data to follow.

Syntax

SELECT column_name FROM table_name WHERE condition;

For instance, only records for employees earning above $2000 should be returned

SELECT first_name, last_name FROM employees WHERE salary > 2000;

The above command returns only those employees who match the specified condition.

Conditions are specified using operators and so the WHERE clause works with operators. There are number of operators in SQL and we are going to see some of these.

Note: The ORDER BY clause can also be used with the WHERE clause for sorting the results as we have also covered it here.

Comparison Operators

Greater than (>)

This operator evaluates to true if the left side of the operator is greater than the right side. For example, we need to return all employees whose salary is beyond 2000. Here is the SQL for this.

SELECT first_name, last_name, salary FROM employees WHERE salary > 2000;

Less than (<)

This operator evaluates to true if the left side of the operator is less than the right side. For example, we need to return all employees whose salary is less than 5000. Here is the SQL for this.

SELECT first_name, last_name, salary FROM employees WHERE salary < 5000;

Lesser or equal (<=)

This operator evaluates to true if the left side of the operator is lesser or equal to the right side. For example, we need to return all employees whose salary is 3000 and below. Here is the SQL for this.

SELECT first_name, last_name, salary FROM employees WHERE salary <= 3000;

Greater or equal (>=)

This operator evaluates to true if the left side of the operator is greater or equal to the right side. For example, we need to return all employees whose salary is 10000 and above. Here is the SQL for this.

SELECT first_name, last_name, salary FROM employees WHERE salary >= 10000;

Equal (=)

This operator evaluates to true if the left side of the operator is equal to the right side. For example, we need to return all employee recs whose salary is 10000. Here is the SQL for this.

SELECT first_name, last_name, salary FROM employees WHERE salary = 10000;

Not equal (<>, !=, ^=)

This operator evaluates to true if the left side of the operator is not equal to the right side. It can be written as <> or != or ^=. For example, we need to return all employees whose salary is not 10000. Here is the SQL for this.

SELECT first_name, last_name, salary FROM employees WHERE salary != 10000;

ANY (SOME)

This operator compares a value to each value in the list. It must be preceded with a comparison operator. For example, we need to return all employees whose salary is 3000, 10000 and 9000. Values in the salary column will be compared to each item in the list. If a salary matches this condition, the record will be returned.

SELECT first_name, last_name, salary FROM employees WHERE salary = ANY (13000, 10000, 9000);

ALL

This operator compares a value to every value in the list. It must be preceded with a comparison operator. For example, we need to return all employees whose salary is 13000, 10000 and 9000. Values in the salary column will be compared to every item in the list. If a salary matches this condition, the record will be returned.

SELECT first_name, last_name, salary FROM employees WHERE salary > ALL (10000, 9000, 13000);

Logical Operators

AND

This operator evaluates to true if the conditions separated by AND are true. For example, we need to return all employee records for employees whose salary is 10000 and the commission is null (don’t earn a commission).

SELECT first_name, last_name, salary FROM employees WHERE salary = 10000 AND commission_pct IS NULL;

NOT

This operator reverts the results in the brackets i.e. if the condition(s) is not true, the operator shows a record. It produces the same results as the inequality operator. For example, For example, we need to return all employees whose salary is not 10000. Here is the SQL for this.

SELECT first_name, last_name, salary FROM employees WHERE NOT(salary = 10000);

OR

This operator evaluates to true if one of the conditions separated by OR is true. For example, we need to return all employee records for employees whose salary is 10000 or the commission is .3 and above.

SELECT first_name, last_name, salary FROM employees WHERE salary = 10000 AND commission_pct >= .3;

Other Operators

IN

This operator evaluates to true if the value exists in the list. It produces the same results with = ANY. For example, we need to return all employee records for employees whose salary is 10000, 9000 and 13000.

SELECT first_name, last_name, salary FROM employees WHERE salary IN (10000, 9000, 13000);

NOT IN

This operator evaluates to true if the value does not exist in the list. It produces the same results with != ALL. For example, we need to return all employee records for employees whose salary is not 10000, 9000 and 13000.

SELECT first_name, last_name, salary FROM employees WHERE salary NOT IN (10000, 9000, 13000);

BETWEEN

This operator tests for the range. Lets say we have two values x and y and we’re testing values between the two, it evaluates to true if the value is greater than or equal to x and lesser than or equal to y. For example, we need to return all employee records for employees whose salary is between 10000 and 15000.

SELECT first_name, last_name, salary FROM employees WHERE salary BETWEEN 10000 AND 15000;

LIKE

This operator tests for pattern matching. It works with wildcards i.e. % and _. Pattern-search character % is used to match any character and any number of characters. Pattern-search _ is used to match a single character. For example, to return all employees whose first_name starts with letter K, we issue this command:

SELECT first_name, last_name, salary FROM employees WHERE first_name LIKE 'K%';

Remember, data in the tables is case-sensitive. Therefore, when pattern matching; ensure you use the right case in the quotes. For example, the command below returns no records even if there are records whose first names start with letter K. We have to write K and not k.

SELECT first_name, last_name, salary FROM employees WHERE first_name LIKE 'k%';

Remember pattern-search character _ represents a single character. This command returns employees whose first name has a u as the second character followed by other character.

SELECT first_name, last_name, salary FROM employees WHERE first_name LIKE '_u%';

IS NULL

This operator evaluates to true if the value is null. For example, we need to check for employees who are not assigned to a manager. The manager_id column reflects the manager assigned to an employee.

SELECT first_name, last_name, manager_id FROM employees WHERE manager_id IS NULL;

IS NOT NULL

This operator evaluates to true if the value is not null. For example, we need to check for employees who are assigned to a manager. The manager_id column reflects the manager assigned to an employee.

SELECT first_name, last_name, manager_id FROM employees WHERE manager_id IS NOT NULL;

DISTINCT (UNIQUE)

This operator eliminates duplicate values i.e. each record must be unique. Uniqueness of a record is based on a complete row of the record and not the first column. We can use the UNIQUE keyword rather than the DISTINCT. For example, to find unique department ids in the employee table, we issue the following query:

SELECT DISTINCT department_id FROM employees;

Summary

1. The WHERE clause is used to restrict retrieved data from the tables.

2. The WHERE clause comes after the table name and before the ORDER BY clause.

3. The WHERE clause works with comparison, logical and other operators.