Subqueries in Oracle
Hey Tech_Bunnies, this blog will walk you through the concepts of subqueries in Oracle.
What is a Subquery?
In Oracle, a subquery is a SQL query that is nested inside another SQL query. Subqueries are also known as nested queries or inner queries. They allow selecting data for a criterion that is developed during the query run-time. Subqueries turn out to be a very clever solution which allows us to use the result set from one query as the input criteria in another query.
A subquery can be nested inside:-
1. WHERE clause
SELECT * FROM employees
WHERE salary = (SELECT max(salary)
FROM employees);
2. FROM clause
SELECT *
FROM (SELECT * FROM employees
ORDER BY salary DESC)
WHERE rownum <= 3;
3. SELECT clause
SELECT product_name, list_price, ROUND( (SELECT AVG( list_price ) FROM products p1 WHERE p1. category_id = p2.category_id), 2) avg_list_price
FROM products p2
Types of subqueries
We have 5 types of subqueries in oracle:
1. Single-row Subquery: This subquery returns zero or a single row. Let’s take a quick example to understand the thing.
Example: Write a query to find the details of employees who are earning the highest salary.
In this query, first, our inner query is executed which returns the result i.e 24000 as the maximum salary, and it is substituted for comparison in WHERE clause. {Quick Tip: We have used the equal-to operator in the WHERE clause as the subquery returns only ONE or NULL value.}
2. Multi-row Subquery: It returns one or more rows as the result. Consider the following example for better understanding.
Example: Write a query to display the details of all the employees whose departments are located at location_id = 1700.
In the above example, the oracle first executes the subquery and fetches the list of departments that are located at location_id 1700. Further, the result is substituted in the outer query to display the details of employees working in either of those departments. {Quick Tip: We have used IN operator as the multivalue subquery fetches one or more rows in the result set}
3. Correlated Subquery: In a correlated subquery, the inner query depends on values provided by the outer query. Basically, the inner query is executed once for each row that might be selected by executing the outer query. Let’s consider the following example for more clarity.
Example: Select the list of employees who earned a higher salary when compared to the average salary of their departments.
In the query, the ‘department_id’ fetched from the ‘employees’ table alias ‘t1’ is compared with the ‘department_id’ retrieved from the ‘employees’ table alias ‘t2’. Hence, our subquery references the external table, ‘employees t1’ table.
4. Multi-column Subquery: In a multi-column subquery, multiple columns are returned as a result. Let’s consider an example of a better and clear understanding.
Our ‘Jobs’ table contains information about the minimum and maximum salary with respect to the ‘job_id’. Therefore, we will find the details of employees who earn the bare minimum allowed salary amount.
Example:
In the above query, the subquery fetches two columns in the result set, i.e. min_salary (minimum salary) and job_id. The following columns are compared with employee’s salary and job_id, and that’s how we search for the employees who earn a bare minimum salary.
5. Inline Views: When a multicolumn subquery is used inside the FROM clause, it is referred to as Inline View, which essentially acts as a virtual or temporary table. Consider the following example.
Example: Display the details of the department with the count of employees are assigned to each department.
In the above example, the inner query creates the virtual table for the outer query which is retained for the duration of execution of the external query. By the implementation of Inline Views, we can join the output from the subquery with the departments table in our database.
I hope this blog gives you a clear understanding of the concept of a subquery in oracle. If this blog helps you understand the concept so share it with your friends and feel free to share your views and drop feedback about the blog. Drop the claps if you liked the blog.
