CoderZone.org

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)

Author: CoderZone
Language: PL/SQL
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.
 
 
There haven't been any comments added for this snippet yet. You may add one if you like.  Add a comment 
© coderzone.org | users online: 9