Quick Search:
 The Oracle WHERE Clause      [Return To Index] Jump to:  


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:

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:

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:

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:

FROM users

Related Links:

Related Code Snippets:
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2023 psoug.org