Oracle Subquery/Correlated Query Examples

Oracle Subquery/Correlated Query Examples

A subquery is a SELECT statement which is used in another SELECT statement. Subqueries are very useful when you need to select rows from a table with a condition that depends on the data of the table itself. You can use the subquery in the SQL clauses including WHERE clause, HAVING clause, FROM clause etc.

The subquery can also be referred as nested SELECT, sub SELECT or inner SELECT. In general, the subquery executes first and its output is used in the main query or outer query.

Types of Sub queries:

There are two types of subqueries in oracle:

• Single Row Subqueries: The subquery returns only one row. Use single row comparison operators like =, > etc while doing comparisions.
• Multiple Row Subqueries: The subquery returns more than one row. Use multiple row comparison operators like IN, ANY, ALL in the comparisons.

Single Row Subquery Examples

1. Write a query to find the salary of employees whose salary is greater than the salary of employee whose id is 100?

SELECT EMPLOYEE_ID,

SALARY

FROM EMPLOYEES

WHERE SALARY >

(

SELECT SALARY

FROM EMPLOYEES

WHERE EMPLOYEED_ID = 100

)

2. Write a query to find the departments in which the least salary is greater than the highest salary in the department of id 200?

SELECT DEPARTMENT_ID,

MIN(SALARY)

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID

HAVING MIN(SALARY) >

(

SELECT MAX(SALARY)

FROM EMPLOYEES

WHERE DEPARTMENT_ID = 200

)
3. Write a query to find the employees who all are earning the highest salary?

SELECT EMPLOYEE_ID,

SALARY

FROM EMPLOYEES

WHERE SALARY =

(

SELECT MAX(SALARY)

FROM EMPLOYEES

)