Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Summarizing by more than
one Relation

SQL Tips by Donald Burleson

The previous section mentioned that

select deptno, count(*) from Emp
group by deptno

could be rewritten into an equivalent form leveraging the correlated scalar subquery:

select distinct deptno,
       (select count(*) from Emp ee
        where ee.deptno = e.deptno)
from Emp e

Both queries project the Emp relation onto the deptno column, and extend the result with one extra column that counts the number of rows in each group in the original relation. What about those deptno values that are missing in the Emp table, shouldn't they be listed with count 0? Suppose deptno, say, 40 is a valid department on the system, how can the query be changed to show it with the count 0?

Well, if deptno = 40 is a valid department, then it should be in some table -- Dept, for example, where it is most likely a primary key. Then, why not use this table in the outer query:

select deptno,
       (select count(*) from Emp e
        where e.deptno = d.deptno)
from Dept d

An added bonus of having two tables in the query is that the distinct qualifier is no longer required.

Hugh Darwen's Summarize

Hugh Darwen argued that group by with aggregation is an operator that requires two tables as the arguments, in general. The idea of introducing such an operator in SQL never caught on. Yet, in each practical situation it might be useful to double check if writing the group by clause as a one- or two- argument operator is more appropriate. 

SQL is notorious for allowing multiple ways to express the same query. Listing all the departments with the employee counts could also be rewritten via the outer join:

select d.deptno, d.dname,
       sum(case when e.deptno is not null then 1 else 0 end)
from Emp e right outer join Dept d
where d.deptno = e.deptno
group by d.deptno, d.dname

If the conditional summation pattern is reduced to a simple count(*), then the departments with no employees will count 1 employee instead of 0. 

ANSI Join Syntax

It is difficult to argue about elegance or ugliness of a certain syntax construction. You just see it or you don't. Comma separated join syntax reflects the fundamental feature of Relational Algebra, which asserts the normal form for select-project-join queries. The only kind of join that escapes it (and therefore, warrants a dedicated syntax) is the outer join.

It is not only aesthetics. It is common for production databases to have columns like CREATED_ON, or COMMENTS across many tables. In this case the NATURAL JOIN syntax is plain dangerous.

As Anthony Molinaro eloquently put it: ?Old style is short and sweet and perfect. ANSI dumbed it down, and for people who've been developing for sometime, it's wholly unnecessary.? 

Which form, scalar subquery or outer join, is more performant? Surely, the answer differs between the vendors. Oracle, for example, is better at outer join optimization than unnesting scalar subqueries in the select clause. Outer join from the optimizer's perspective has almost the same rights as normal join. It can be permuted with the other joins in the join order; it is costed similarly, etc. If the summarizing query is a part of the other query, chances are the optimizer may find the better plan when the query is written via outer join. 



This is an excerpt from the new book SQL Design Patterns: The Expert Guide to SQL Programming by Vadim Tropashko

You can buy it direct from the publisher for 30%-off.


Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.