Oracle Subquery

Subquery is a query with in a query.Syntax:-select * from table_name where column_name in (select column from table_name)
Highlighted part is called Subquery.

Consider below table structures.

DEPT table

Deptno Deptname Location
1 HR London
2 Marketing Newyork
5 Management Bejing

EMP table

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 the details of all ‘HR’ employees.
Select * from EMP where deptno in (select deptno from DEPT  where deptname = ‘HR’);
Always Subquery will be executed first. If Subquery returns no data, main query also will not return any data.

2. Find the details of all ‘Logistic’ department employees if any.

Select * from EMP where deptno in (select deptno from DEPT  where deptname = ‘Logistic’);

The above query will not return any records because Subquery has no matching records

3. Find the details of all employees earning lowest salary.
Select * from EMP where salary = (select min(salary) from EMP) ;

4.  Find all employee details if there any Marketing dept

 Select * from EMP e where exists (select * from DEPT d where deptname = ‘Marketing’);   

Subquery returns more than one row error
Example 3 will return details of all employees earning lowest salary. While writing the subquery user forgot to mention min clause and submitted below query.

SQL> Select * from EMP where salary = (select salary from EMP) ;  

ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

This error happened because main query expecting a single value from the Subquery but returning more than one row. When writing a single-row Subquery make sure it is returning single row.

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: Logo

You are commenting using your 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