How does Subquery in select statement work in oracle

Question

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.

Solution

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.