Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How does Subquery in select statement work in oracle

I have looked all over for an explanation, to how does the subquery in a select statement work and still I cannot grasp the concept because of very vague explanations.

I would like to know how do you use a subquery in a select statement in oracle and what exactly does it output.

For example, if i had a query that wanted to display the names of employees and the number of profiles they manage from these tables

Employee(EmpName, EmpId)

Profile(ProfileId, ..., EmpId)

how do I use the subquery?

I was thinking a subquery is needed in the select statement to implement the group by function to count the number of profiles being managed for each employee, but I am not too sure.

like image 326
user3054901 Avatar asked Dec 13 '13 02:12

user3054901


People also ask

How does subquery work in Oracle?

A subquery nested in the WHERE clause of the SELECT statement is called a nested subquery. A subquery can contain another subquery. Oracle allows you to have an unlimited number of subquery levels in the FROM clause of the top-level query and up to 255 subquery levels in the WHERE clause.

Can you do a subquery in a SELECT statement?

A subquery-also referred to as an inner query or inner select-is a SELECT statement embedded within a data manipulation language (DML) statement or nested within another subquery. You can use subqueries in SELECT, INSERT, UPDATE, and DELETE statements wherever expressions are allowed.

How subqueries are executed Oracle?

Working of Oracle Subquery Whenever we write a subquery, the subquery should be enclosed within the brackets or parentheses (). So, when the oracle gets a query which has also a subquery, it first executes the subquery and retrieves the result and then uses the same result in the outer query.

Can we use subquery in if statement in Oracle?

You cannot include SQL inside an IF statement. hi, Collect the sub query result to a collection and loop through it. It is the feasible solution in this case.


1 Answers

It's simple-

SELECT empname,        empid,        (SELECT COUNT (profileid)           FROM profile          WHERE profile.empid = employee.empid)            AS number_of_profiles   FROM employee; 

It is even simpler when you use a table join like this:

  SELECT e.empname, e.empid, COUNT (p.profileid) AS number_of_profiles     FROM employee e LEFT JOIN profile p ON e.empid = p.empid GROUP BY e.empname, e.empid; 

Explanation for the subquery:

Essentially, a subquery in a select gets a scalar value and passes it to the main query. A subquery in select is not allowed to pass more than one row and more than one column, which is a restriction. Here, we are passing a count to the main query, which, as we know, would always be only a number- a scalar value. If a value is not found, the subquery returns null to the main query. Moreover, a subquery can access columns from the from clause of the main query, as shown in my query where employee.empid is passed from the outer query to the inner query.


Edit:

When you use a subquery in a select clause, Oracle essentially treats it as a left join (you can see this in the explain plan for your query), with the cardinality of the rows being just one on the right for every row in the left.


Explanation for the left join

A left join is very handy, especially when you want to replace the select subquery due to its restrictions. There are no restrictions here on the number of rows of the tables in either side of the LEFT JOIN keyword.

For more information read Oracle Docs on subqueries and left join or left outer join.

like image 163
Rachcha Avatar answered Sep 20 '22 17:09

Rachcha