Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: IN Function Jump to:  
Category: >> Oracle PL/SQL >> IN Function Bookmark and Share

<< lastnext >>

Snippet Name: IN Function

Description: The IN function helps reduce the need to use multiple OR conditions.

Also see:
» FUNCTIONS: Deterministic
» FUNCTIONS: Nested Functions
» FUNCTIONS: IF statement
» FUNCTIONS: date/time
» FUNCTIONS: Sample functions
» FUNCTIONS: drop
» FUNCTIONS: Recompile
» FUNCTIONS: DEBUG mode
» FUNCTIONS: IN OUT parameter
» FUNCTIONS: with output parameters
» FUNCTIONS: with parameters
» FUNCTIONS: without parameters
» FUNCTIONS: Create function
» FUNCTIONS: special restrictions
» FUNCTIONS: System Privileges
» Built-In Functions: CASE
» Built-In Functions: DECODE
» SUBST and INSTR together
» INSTR (InString)
» SUBSTR (SubString)
» String Functions: REVERSE
» String Functions: LENGTH
» String Functions: INSTR
» String Functions: CONCAT
» String Functions: CHAR
» String Functions: INITCAP
» String Functions: LOWER
» String Functions: UPPER
» Date Functions: NUMTOYMINTERVAL
» Date Functions: NUMTODSINTERVAL

Comment: (none)

Language:
Highlight Mode: PLSQL
Last Modified: March 10th, 2009

-- The syntax for the IN function is:
 
SELECT columns
FROM tables
WHERE column1 IN (value1, value2, .... value_n);
 
-- This SQL statement will return the records where column1 is 
-- value1, value2..., or value_n. The IN function can be used 
-- in any valid SQL statement - select, insert, update, or delete.
 
 
 
-- Example #1
 
-- The following is an SQL statement that uses the IN function:
 
SELECT *
FROM suppliers
WHERE supplier_name IN ( 'IBM', 'Hewlett Packard', 'Microsoft');
 
-- This would return all rows where the supplier_name is either 
-- IBM, Hewlett Packard, or Microsoft. Because the * is used in 
-- the select, all fields from the suppliers table would appear 
-- in the result set.
 
-- It is equivalent to the following statement:
 
SELECT *
FROM suppliers
WHERE supplier_name = 'IBM'
OR supplier_name = 'Hewlett Packard'
OR supplier_name = 'Microsoft';
 
-- As you can see, using the IN function makes the statement 
-- easier to read and more efficient.
 
 
 
-- Example #2
 
-- You can also use the IN function with numeric values.
 
SELECT *
FROM orders
WHERE order_id IN (10000, 10001, 10003, 10005);
 
-- This SQL statement would return all orders where the order_id 
-- is either 10000, 10001, 10003, or 10005.
 
-- It is equivalent to the following statement:
 
SELECT *
FROM orders
WHERE order_id = 10000
OR order_id = 10001
OR order_id = 10003
OR order_id = 10005;
 
 
 
-- Example #3 using "NOT IN"
 
-- The IN function can also be combined with the NOT operator.
 
-- For example,
 
SELECT *
FROM suppliers
WHERE supplier_name NOT IN ( 'IBM', 'Hewlett Packard', 
'Microsoft');
 
-- This would return all rows where the supplier_name is neither 
-- IBM, Hewlett Packard, or Microsoft. Sometimes, it is more 
-- efficient to list the values that you do not want, as opposed 
-- to the values that you do want.
 


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 217 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?