Database   Sql   Functions   Packages   Performance   Books   Oracle   Other   About   XML   ORA-messages
Oracle sql in clause


Oracle Regular Expressions
SQL Date format
String concatenation
Loop in pl/sql
SQL IN-clause
Regular Expressions Examples
Flashback query
Grant/revoke privileges
Rename tables, columns
Insert into Oracle
Database name
Table with sequenced numbers
Oracle connect by
Add columns to table


Oracle SQL in clause

In a Oracle SQL-statement you can use the IN-clause.
For example:
select *
from table
where column in ('1','2','3')
or    column in (select val
                 from   table2);
In Oracle 10g the number of static values in the IN-clause is limited to 1000.
If you need to use more than 1000 values for the where condition, you can populate a temp table with the values and use a select on that table in the IN-clause.

Most of the time an IN-clause can also be rewritten to an EXISTS-clause. Sometimes the performance of the 2 is very different. Example:
select *
from table a
where EXISTS(select ''
             from   table2 b
			 where  b.kol = a.kol);