From the course: Oracle Database 12c: Advanced SQL

Unlock the full course today

Join today to access over 22,600 courses taught by industry experts or purchase this course individually.

Using multiple-value subqueries

Using multiple-value subqueries - Oracle Database Tutorial

From the course: Oracle Database 12c: Advanced SQL

Start my 1-month free trial

Using multiple-value subqueries

“

- In our previous video, we saw how we use a simple sub-query to return the employee earning the highest salary in our company. Since our sub-query is restricted to returning a single value, because we use either a min or a max function, it made sense to pair it with an equal operator. However, a sub-query can also return multiple rows. For example, let's say we want to obtain the details of all employees working in all departments in a specific location. We can start by writing a sub-query that will return the department ID for all departments in a specific location, say, location code 1700. So I'll type select department id from departments where location id equals 1700. This query, as a stand alone query, just returned a list of department IDs that are located within location id 1700, wherever that may be. And we can see, we have multiple departments that reside in this location. We can now nest this query as a sub-query in an external query. So I can type: select, first name, last…

Contents