Subqueries and Types of Subqueries in Oracle (Basic 1 - 5)

Subqueries are queries that are embedded in another query. They can be used to perform a variety of tasks, such as filtering data, calculating aggregate values, and joining tables.

There are two main types of subqueries: single-row subqueries and multiple-row subqueries.

Single-row subqueries return a single row of data. They are often used to compare values in the outer query to values in the subquery. For example, the following query uses a single-row subquery to find the employees whose salary is greater than the highest salary in the department:

SQL

SELECT employee_id, salary
FROM employees
WHERE salary > (SELECT MAX(salary) FROM employees WHERE department_id = employees.department_id);

Multiple-row subqueries return multiple rows of data. They are often used to filter data from the outer query based on the results of the subquery. For example, the following query uses a multiple-row subquery to find the employees who work in the same department as employee number 123:

SQL

SELECT employee_id, department_id
FROM employees
WHERE department_id IN (SELECT department_id FROM employees WHERE employee_id = 123);

Subqueries can also be nested, meaning that a subquery can contain another subquery. Nested subqueries are often used to perform complex calculations. For example, the following query uses a nested subquery to find the average salary for each department:

SQL

SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

Subqueries can be a powerful tool for querying data in Oracle. By understanding the different types of subqueries and how to use them, you can write more efficient and effective queries.

Example of a correlated subquery:

The following query uses a correlated subquery to find the employees who have a higher salary than any other employee in their department:

SQL

SELECT employee_id, salary
FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = employees.department_id);

Correlated subqueries are more complex than non-correlated subqueries, and they can be slower to execute. However, they can be useful for performing queries that cannot be performed with non-correlated subqueries.

Example of a scalar subquery:

The following query uses a scalar subquery to find the total salary for each department:

SQL

SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
HAVING SUM(salary) > (SELECT SUM(salary) FROM employees);

Scalar subqueries are often used to perform calculations on data from multiple tables.

I hope this helps!