From the course: Oracle Database 12c: Advanced SQL

Introduction to subqueries - Oracle Database Tutorial

From the course: Oracle Database 12c: Advanced SQL

Start my 1-month free trial

Introduction to subqueries

“

- [Instructor] In Oracle, a subquery is a SQL query which is nested inside another SQL query. Subqueries can reside in the WHERE clause of your SQL query or they can also reside in the FROM clause of your SQL query and they can also reside in the SELECT clause. Subqueries are a clever solution that allows us to use the result set from one query's output as the input for another query. Most often, your subqueries will probably be found inside the WHERE clause of your SQL statement and these types are also called nested subqueries. For example, say we are interested in finding out which employee has the highest salary in our company. We will first need to determine which salary is the highest and then find the employee earning that salary. We can use subqueries to achieve that goal. We will start by writing a query that returns the top salary earned by all employees in our company. We can now embed this query as part of another query's WHERE clause. Oracle will first execute the internal nested subquery. Then once returning the highest salary in our company, Oracle will feed that value that is the output of the nested query into the external or outside query, thus returning the details of the employee that is earning the highest salary in our company. Essentially, Oracle will be embedding the results of the subquery directly into the query text of our external query. We can also embed a subquery in the FROM clause of a SQL statement. These are called inline views. Inline views are commonly used to simplify complex queries by removing join operators and condensing several queries into a single unified query. Think of inline views as if your query was a table. Let's say we want to find the top three highest earning employees in our company. We can first create query that ranks the employees from our employee table based on their salaries as highlighted in the orange box on your screen. We can create a query that ranks the employees from our employees table based on their salaries from highest earning to lowest earning. We can now embed this query as a subquery or inline view inside the FROM clause of a SQL statement. As when using subqueries in the WHERE clause, Oracle will first execute the subquery and essentially create a virtual table or record set of the results that are returned. Then Oracle will run the external table on top of the returned result set. The use of ROWNUM in our external query restricts the result set to the first three rows. However, since the output of the subquery is an ordered list of records based on the employee salary, the first three rows are also the top earning employees, a very elegant solution. So let's go ahead and see subqueries in action.

Contents