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 employees who all are earning the highest salary?

SELECT EMPLOYEE_ID,

 SALARY

FROM EMPLOYEES

WHERE SALARY =

  (

  SELECT  MAX(SALARY)

  FROM EMPLOYEES

  )

3. 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

  )

Multiple Row Subquery Examples

1. Write a query to find the employees whose salary is equal to the salary of at least one employee in department of id 300?

SELECT EMPLOYEE_ID,

 SALARY

FROM EMPLOYEES

WHERE SALARY IN

  (

  SELECT  SALARY

  FROM EMPLOYEES

  WHERE DEPARTMENT_ID = 300

  )

2. Write a query to find the employees whose salary is greater than at least on employee in department of id 500?

SELECT EMPLOYEE_ID,

 SALARY

FROM EMPLOYEES

WHERE SALARY > ANY

  (

  SELECT  SALARY

  FROM EMPLOYEES

  WHERE DEPARTMENT_ID = 500

  )

3. Write a query to find the employees whose salary is less than the salary of all employees in department of id 100?

SELECT EMPLOYEE_ID,

 SALARY

FROM EMPLOYEES

WHERE SALARY < ALL

  (

  SELECT  SALARY

  FROM EMPLOYEES

  WHERE DEPARTMENT_ID = 100

  )

4. Write a query to find the employees whose manager and department should match with the employee of id 20 or 30?

SELECT EMPLOYEE_ID,

 MANAGER_ID,

 DEPARTMENT_ID

FROM EMPLOYEES

WHERE (MANAGER_ID,DEPARTMENT_ID) IN

  (

  SELECT MANAGER_ID,

   DEPARTMENT_ID

  FROM EMPLOYEES

  WHERE EMPLOYEE_ID IN (20,30)

  )

5. Write a query to get the department name of an employee?

SELECT EMPLOYEE_ID,

 DEPARTMENT_ID,

 (SELECT DEPARTMENT_NAME

 FROM DEPARTMENTS D

 WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID

 )

FROM EMPLOYEES E

Correlated SubQueries Examples

Correlated sub query is used for row by row processing. The sub query is executed for each row of the main query.

1. Write a query to find the highest earning employee in each department?

SELECT DEPARTMENT_ID,

 EMPLOYEE_ID,

 SALARY

FROM EMPLOYEES E_0

WHERE 1 =

  (

  SELECT  COUNT(DISTINCT SALARY)

  FROM EMPLOYEES E_I

  WHERE E_O.DEPARTMENT_ID = E_I.DEPARTMENT_ID

  AND E_O.SALARY <=  E_I.SALARY

  )

2. Write a query to list the department names which have at lease one employee?

SELECT DEPARTMENT_ID,

 DEPARTMENT_NAME

FROM DEPARTMENTS D

WHERE EXISTS

 (

 SELECT 1

 FROM EMPLOYEES E

 WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID)

3. Write a query to find the departments which do not have employees at all?

SELECT DEPARTMENT_ID,

 DEPARTMENT_NAME

FROM DEPARTMENTS D

WHERE NOT EXISTS

 (

 SELECT  1

 FROM EMPLOYEES E

 WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID)

SQL Inline View Subquery

When you use SQL Subquery in From clause of the select statement it is called inline view.

A common use for inline views in Oracle SQL is to simplify complex queries by removing join operations and condensing several separate queries into a single query. A subquery which is enclosed in parenthesis in the FROM clause may be given an alias name. The columns selected in the subquerycan be referenced in the parent query, just as you would select from any normal table or view.

Example 1 of Inline View

Display the top five earner names and salaries from the EMPLOYEES table:

SELECT ROWNUM as RANK, last_name, salary
FROM (SELECT last_name, salary
      FROM employees
      ORDER BY salary DESC)
WHERE ROWNUM <= 5;

Example 2 of Inline View

Calculate the number of employees in each department

SELECT d.dept_id, d.name, emp_cnt.tot
FROM department d, (SELECT dept_id, count(*) tot
                    FROM employee
                    GROUP BY dept_id) emp_cnt
WHERE d.dept_id = emp_cnt.dept_id;