Let’s solve a fairly simple SQL problem:
For each department count the number of people hired on Monday
• Use scott.emp table
• Show department number and count columns
• If no employees from a given department was hired on Monday, we should list such
department with 0 in the count column
• Sort the result by the department number
Expected Result:
| DEPTNO | MON_HIRES |
|---|---|
| 10 | 0 |
| 20 | 0 |
| 30 | 1 |
We will start with the in-line scalar subquery approach as it is probably one of the most intuitive:
Strategy #1: In-Line Scalar Subquery
SELECT deptno, (SELECT COUNT(*)
FROM scott.emp
WHERE deptno=e.deptno
AND TO_CHAR(hiredate, 'DY')='MON') mon_hires
FROM scott.emp e
GROUP BY deptno
ORDER BY 1
When you only need a single value/column/expression from a correlated subquery, the in-line subquery in SELECT clause works just fine. If we needed more than one: count and let say total salary, we would need to use LATERAL view (or a completely different approach – see below).
Strategy #2: Lateral View
WITH x AS (
SELECT DISTINCT deptno
FROM scott.emp
)
SELECT x.deptno, m.mon_hires, m.total_sal
FROM x, LATERAL (SELECT COUNT(*) mon_hires, SUM(sal) total_sal
FROM scott.emp e
WHERE e.deptno=x.deptno
AND TO_CHAR(e.hiredate, 'DY')='MON') m
ORDER BY 1
Result:
| DEPTNO | MON_HIRES | TOTAL_SAL |
|---|---|---|
| 10 | 0 | – |
| 20 | 0 | – |
| 30 | 1 | 1250 |
So far, all is good. I know that some database developers don’t like using table aliases too much and when an opportunity comes they use ANSI standard JOIN syntax with USING clause. Can it be applied in the lateral view?
WITH x AS (
SELECT DISTINCT deptno
FROM scott.emp
)
SELECT x.deptno, m.mon_hires
FROM x, LATERAL (SELECT COUNT(empno) mon_hires
FROM scott.emp JOIN x USING (deptno)
WHERE TO_CHAR(hiredate, 'DY')='MON') m
ORDER BY 1
Result:
| DEPTNO | MON_HIRES |
|---|---|
| 10 | 1 |
| 20 | 1 |
| 30 | 1 |
The syntax is correct but the result is apparently not! What happened?
We replaced the WHERE clause condition of e.deptno=x.deptno with the JOIN x USING(deptno) – it first looked legitimate to me until I realized that we have just added an extra join instead of a reference to an external table (CTE x). Essentially, our last (incorrect) query is the same as the following:
WITH x AS (
SELECT DISTINCT deptno
FROM scott.emp
)
SELECT x.deptno, m.mon_hires
FROM x, LATERAL (SELECT COUNT(*) mon_hires
FROM scott.emp e, x
WHERE e.deptno=x.deptno
AND TO_CHAR(e.hiredate, 'DY')='MON') m
ORDER BY 1
We simply introduced a new (and unwanted) join on the CTE x and turned the correlated reference (in the WHERE clause) into an old-style joining condition which did not even require the LATERAL view functionality:
WITH x AS (
SELECT DISTINCT deptno
FROM scott.emp
)
SELECT x.deptno, m.mon_hires
FROM x, (SELECT COUNT(*) mon_hires
FROM scott.emp e, x
WHERE e.deptno=x.deptno
AND TO_CHAR(e.hiredate, 'DY')='MON') m
ORDER BY 1
Result:
| DEPTNO | MON_HIRES |
|---|---|
| 10 | 1 |
| 20 | 1 |
| 30 | 1 |
The above examples prove that we need to be very careful when mixing up different techniques and new syntax options.
Finally, the last approach will show that only a single scan of the emp table is needed to get the result:
Strategy #3: Conditional Counting
SELECT deptno, COUNT(DECODE(TO_CHAR(hiredate, 'DY'), 'MON', 1)) mon_hires FROM scott.emp GROUP BY deptno ORDER BY 1
Result:
| DEPTNO | MON_HIRES |
|---|---|
| 10 | 0 |
| 20 | 0 |
| 30 | 1 |
This is the best and incidentally the shortest solution that once again demonstrates the power of conditional counting (aggregation) right in SELECT clause.
Likewise we can also show the total salary of those hired on Monday:
SELECT deptno,
COUNT(DECODE(TO_CHAR(hiredate, 'DY'), 'MON', 1)) mon_hires,
SUM(DECODE(TO_CHAR(hiredate, 'DY'), 'MON', sal)) total_sal
FROM scott.emp
GROUP BY deptno
ORDER BY 1
Result:
| DEPTNO | MON_HIRES | TOTAL_SAL |
|---|---|---|
| 10 | 0 | – |
| 20 | 0 | – |
| 30 | 1 | 1250 |
***
If you find this post useful, please press the LIKE button and subscribe.
My Oracle Group on Facebook:
Also, you may want to join my Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/
Suggested Reading:
Would you like to read about many more tricks and puzzles? For more clever tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds”.
sqlpatterns 