Term: WHERE

Definition:
The Oracle PL/SQL WHERE clause is used to select data conditionally, by specifying the rows or records that you want to match. The WHERE clause works like a filter to include or deny rows based on conditions you define.

Example Syntax:

SELECT column(s) 

FROM table 

WHERE column condition(s);

For example, lets say you have a table named 'users' that contains a list of clients. If you execute the following SQL, it will return all of the rows in the table:

SELECT first_name 

FROM users;

If you want to find only rows where the user's first name is 'john', you would add a WHERE clause condition that tells the database to match only those rows:

SELECT * 

FROM users

WHERE first_name = 'john';

To find all rows where the first name is NOT 'john', you could use the "not equal" operator, as shown in this SQL:

SELECT * 

FROM users

WHERE first_name <> 'john';

(The '<>' means 'NOT EQUAL TO'.)

To find all rows where the first name is 'john' and the city is 'Boston', you could use this query:

SELECT * 

FROM users

WHERE first_name = 'john'

AND city = 'boston';

Multiple conditions can be added to the WHERE clause, including sub-queries that can contain their own WHERE clauses. A sub-query is basically a query within a query. They are also called "nested queries" or "sub-selects". To be valid, all sub-queries must be enclosed in parentheses.

There are 3 basic types of sub-queries in SQL, and sub-queries in the WHERE clause are referred to as "predicate" sub-queries. Predicate sub-queries can be used only in the HAVING and WHERE clauses, and these sub-queries must retrieve one column.

In the following example the sub-query is being used to retrieve records from the users table that are also listed in the admin table:

SELECT * 

FROM users 

WHERE U_ID

  IN

(SELECT U_ID FROM admin);

Related Links:

The Oracle WHERE Clause

Related Code Snippets:

Date Functions: WHERE Clause Joins - Examples of using dates in WHERE Clause Joins.
SELECT with WHERE Clause - The WHERE clause is used to screen or filter records for specific matches.
WHERE Clause: Conditions - The WHERE Clause servers two different purposes. One is to limit the re...
WHERE Clause: Joins - The WHERE Clause servers two different purposes. One is to join two or more ...