Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle WHERE Clause      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

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:

Related Code Snippets:
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 281 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?