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.
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.
~ Answered on 2013-12-13 02:13:29