Oracle IN clause

IN operator uses to specify one or more values in where clause.

Syntax:
select columns from table_name where column_name IN ( values )
Examples:- 

Consider below EMP table structure

Empno Empname Salary Deptno
10 Bill 12000 5
11 Solomon 10000 5
12 Susan 10000 5
13 Wendy 9000 1
14 Benjamin 7500 1
15 Tom 7600 1
16 Henry 8500 2
17 Robert 9500 2
18 Paul 7700 2

1. Find all details of Bill, Solomon and Wendy employee.
Select * from EMP where empname INBill’ , ‘Solomon’ ,‘Wendy’);

2. Find details of employee earning highest salary.
Select * from EMP where salary IN (select max(salary) from EMP) ;
3. Find details of employees earning less than highest salary.
Select * from EMP where salary NOT IN (select max(salary) from EMP) ;
Not that NOT IN operator is a negation of IN operator.
Advertisements

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s