Day 2 - Pitfalls Roundup 2 (Execution Order and Joins)

📌 Key Takeaways

• A SELECT statement is processed not in the order it is written but in the order FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. A SELECT alias can be used only in ORDER BY, which comes after it.
• WHERE runs before grouping, so it cannot use aggregate functions (ORA-00934), and the SELECT after GROUP BY can contain only grouping columns and aggregates (ORA-00979).
• ROWNUM is assigned at the WHERE stage and ORDER BY comes last. To cut a Top N after sorting, sort first in an inline view. ROWNUM = 2 and ROWNUM > 1 are not errors but return 0 rows.
• Joining n tables needs at least n−1 join conditions. Each missing one creates a Cartesian product to that extent. Put (+) on the side that will be filled with NULL (the deficient side).
• If you put a condition on the inner table of an outer join in WHERE, the NULL rows drop out and it behaves like an inner join. If a single-row subquery returns two or more rows, you get ORA-01427, and BETWEEN includes both endpoints.

Written Order and Processing Order

A SELECT statement starts with SELECT, but Oracle processes it in a different order. Execution-order problems are solved by recasting them as "is that name visible in this clause?"

This one table explains the four errors and behaviors below.

Aliases Only in ORDER BY

ORDER BY comes after SELECT, so the alias already exists.

SELECT ENAME, SAL * 12 AS ANNUAL FROM EMP WHERE DEPTNO = 10 ORDER BY ANNUAL DESC;

ENAME          ANNUAL
---------- ----------
KING            60000
CLARK           29400
MILLER          15600

If you use the same alias in WHERE, it is a name that has not been created yet.

SELECT ENAME, SAL * 12 AS ANNUAL FROM EMP WHERE ANNUAL > 30000;

SELECT ENAME, SAL * 12 AS ANNUAL FROM EMP WHERE ANNUAL > 30000
                                                *
ERROR at line 1:
ORA-00904: "ANNUAL": invalid identifier

• In WHERE, rewrite the expression itself instead of the alias (WHERE SAL * 12 > 30000).
• The exam judges by the rule that SELECT aliases cannot be used in GROUP BY or HAVING either. The Oracle 23ai in our practice lab allows GROUP BY alias and HAVING alias, so it runs without error (W4 Day 2). In versions before 23ai, it is ORA-00904.

Aggregate Functions Cannot Be Used in WHERE

WHERE is the stage before grouping rows. The per-department average has not been computed yet.

SELECT DEPTNO, AVG(SAL) FROM EMP WHERE AVG(SAL) > 2000 GROUP BY DEPTNO;

SELECT DEPTNO, AVG(SAL) FROM EMP WHERE AVG(SAL) > 2000 GROUP BY DEPTNO
                                       *
ERROR at line 1:
ORA-00934: group function is not allowed here

Move the aggregate condition to HAVING, which filters after grouping.

SELECT DEPTNO, ROUND(AVG(SAL)) AS AVG_SAL FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) > 2000 ORDER BY AVG_SAL DESC;

    DEPTNO    AVG_SAL
---------- ----------
        10       2917
        20       2175

Columns Not in GROUP BY Cannot Be Used in SELECT

When you group by department, one department becomes one row. You cannot decide which of the several employee names to show in that one row.

SELECT DEPTNO, ENAME, SUM(SAL) FROM EMP GROUP BY DEPTNO;

SELECT DEPTNO, ENAME, SUM(SAL) FROM EMP GROUP BY DEPTNO
               *
ERROR at line 1:
ORA-00979: "ENAME": must appear in the GROUP BY clause or be used in an aggregate function

• The textbook wording is not a GROUP BY expression. 23ai also tells you the name of the offending column. The number is the same.
• The opposite direction is fine. Leaving a column that is in GROUP BY out of SELECT is not an error.

A Non-Aggregate Condition in HAVING

Writing a condition on a grouping column rather than an aggregate in HAVING is not an error either.

SELECT DEPTNO, COUNT(*) AS CNT FROM EMP GROUP BY DEPTNO HAVING DEPTNO > 10 ORDER BY DEPTNO;

    DEPTNO        CNT
---------- ----------
        20          5
        30          6

• The result is the same as filtering first with WHERE DEPTNO > 10 and then grouping. The WHERE version reduces rows before grouping, and the HAVING version discards groups after everything is grouped.
• The only columns you can use outside an aggregate in HAVING are grouping columns. If you use an ungrouped column, as in HAVING SAL > 1000, it is ORA-00979.

ROWNUM Is Assigned Before Sorting

ROWNUM is a number assigned to rows that pass WHERE. ORDER BY is last in processing order, so within the same SELECT, numbers are assigned and the cut is made first, and then sorting happens.

SELECT ROWNUM, ENAME, SAL FROM EMP WHERE ROWNUM <= 3 ORDER BY SAL DESC;

    ROWNUM ENAME             SAL
---------- ---------- ----------
         2 ALLEN            1600
         3 WARD             1250
         1 SMITH             800

• It first cut the 3 rows that were read first (SMITH, ALLEN, and WARD in the practice container) and then sorted only those 3 rows. These are not the top 3 by salary. Which 3 rows get read first is not guaranteed either.
• The numbers appearing in the shuffled order 2, 3, 1 is the proof that "numbers were assigned before sorting."

If you move the sort inside an inline view, the numbers are assigned to the sorted result.

SELECT ROWNUM AS RN, ENAME, SAL FROM (SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC, ENAME) WHERE ROWNUM <= 3;

        RN ENAME             SAL
---------- ---------- ----------
         1 KING             5000
         2 FORD             3000
         3 SCOTT            3000

With ROWNUM = 2, the first row receives number 1 and is rejected, then the next row becomes a candidate for number 1 again, so number 2 is never created (W6 Day 3).

SELECT COUNT(*) FROM EMP WHERE ROWNUM = 2;

  COUNT(*)
----------
         0

• ROWNUM > 1 is also 0 rows for the same reason. The fact that it does not raise an error is where the answer choices diverge.

When Join Conditions Are Missing

Cartesian Product and n−1 Conditions

If you list tables in FROM without a join condition, every row pairs with every other row. That is 14 rows of EMP × 4 rows of DEPT.

SELECT COUNT(*) FROM EMP, DEPT;

  COUNT(*)
----------
        56

If you join three tables but write only one condition, a product still arises with the table that has no condition. SALGRADE has 5 rows.

SELECT COUNT(*) FROM EMP E, DEPT D, SALGRADE S WHERE E.DEPTNO = D.DEPTNO;

  COUNT(*)
----------
        70

SELECT COUNT(*) FROM EMP E, DEPT D, SALGRADE S WHERE E.DEPTNO = D.DEPTNO AND E.SAL BETWEEN S.LOSAL AND S.HISAL;

  COUNT(*)
----------
        14

• The second condition is BETWEEN, not =. The n−1 is the number of conditions, and they need not be equality (a non-equi join).

(+) Goes on the Deficient Side

(+) goes on the side that will be padded with a NULL row when there is no match. If you want to keep all departments, leave DEPT as it is and put (+) on the EMP-side column.

• If you memorize it as "attach it to the side you keep," you get it backwards. The side with the symbol is the side that is short and gets supplemented.

WHERE After an Outer Join

We want to attach CLERK employees to each department while still showing departments that have no CLERK. In the (+) notation, you must put (+) on every EMP-side condition for the outer join to hold.

SELECT D.DNAME, E.ENAME FROM DEPT D, EMP E WHERE D.DEPTNO = E.DEPTNO(+) AND E.JOB(+) = 'CLERK' ORDER BY D.DNAME, E.ENAME;

DNAME          ENAME
-------------- ----------
ACCOUNTING     MILLER
OPERATIONS
RESEARCH       ADAMS
RESEARCH       SMITH
SALES          JAMES

If you leave out one (+) on E.JOB, OPERATIONS disappears.

SELECT D.DNAME, E.ENAME FROM DEPT D, EMP E WHERE D.DEPTNO = E.DEPTNO(+) AND E.JOB = 'CLERK' ORDER BY D.DNAME, E.ENAME;

DNAME          ENAME
-------------- ----------
ACCOUNTING     MILLER
RESEARCH       ADAMS
RESEARCH       SMITH
SALES          JAMES

• For the OPERATIONS row, the outer join filled E.JOB with NULL. Then testing NULL = 'CLERK' is not true, so it drops out. The result is the same as an inner join.
• In ANSI notation, the same difference arises between ON and WHERE. LEFT JOIN EMP E ON D.DEPTNO = E.DEPTNO AND E.JOB = 'CLERK' gives the 5 rows above, and ON D.DEPTNO = E.DEPTNO WHERE E.JOB = 'CLERK' gives 4 rows. In the practice container, each produced the same rows as the two results above.

⚠️ Pitfall: In an outer join problem, if WHERE has a condition on a column of the table that will be NULL-padded without (+), the number of result rows is the same as an inner join even though you used an outer join. When counting rows for the answer choices, remove the NULL rows first.

Do Not Put an Alias on the Common Column of NATURAL JOIN or USING

NATURAL JOIN and USING merge the common column into one. The merged column belongs to neither table, so attaching E. or D. is an error (W4 Day 5).

SELECT D.DEPTNO, DNAME FROM EMP E JOIN DEPT D USING (DEPTNO) WHERE E.ENAME = 'KING';

SELECT D.DEPTNO, DNAME FROM EMP E JOIN DEPT D USING (DEPTNO) WHERE E.ENAME = 'KING'
       *
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier

SELECT E.DEPTNO, DNAME FROM EMP E NATURAL JOIN DEPT D WHERE ENAME = 'KING';

SELECT E.DEPTNO, DNAME FROM EMP E NATURAL JOIN DEPT D WHERE ENAME = 'KING'
       *
ERROR at line 1:
ORA-25155: column used in NATURAL join cannot have qualifier

If you remove the alias, both run. You may attach an alias to E.ENAME, which is not a common column.

SELECT DEPTNO, DNAME FROM EMP E JOIN DEPT D USING (DEPTNO) WHERE E.ENAME = 'KING';

    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING

Subqueries and Set Operators

Multiple Rows for a Single-Row Operator

A subquery after a single-row operator such as = or > must return one row or fewer. The two ANALYSTs both have a salary of 3000, but there are two rows.

SELECT ENAME, SAL FROM EMP WHERE SAL = (SELECT SAL FROM EMP WHERE JOB = 'ANALYST');

SELECT ENAME, SAL FROM EMP WHERE SAL = (SELECT SAL FROM EMP WHERE JOB = 'ANALYST')
                                        *
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

• Even if the values are the same, it is judged by the number of rows. The number of rows is determined only by running it, so this is a runtime error rather than a parse error.
• If the subquery returns 0 rows, it is not an error. The comparison target becomes NULL and the result is 0 rows. The COUNT(*) of WHERE SAL = (SELECT SAL FROM EMP WHERE DEPTNO = 40) was 0.
• To accept multiple rows, use IN. SAL IN (SELECT SAL FROM EMP WHERE JOB = 'ANALYST') gives the two rows FORD and SCOTT.

UNION and UNION ALL

We stack the jobs in department 10 (3 rows) and department 20 (5 rows) one above the other.

SELECT JOB FROM EMP WHERE DEPTNO = 10 UNION SELECT JOB FROM EMP WHERE DEPTNO = 20 ORDER BY JOB;

JOB
---------
ANALYST
CLERK
MANAGER
PRESIDENT

SELECT JOB FROM EMP WHERE DEPTNO = 10 UNION ALL SELECT JOB FROM EMP WHERE DEPTNO = 20 ORDER BY JOB;

JOB
---------
ANALYST
ANALYST
CLERK
CLERK
CLERK
MANAGER
MANAGER
PRESIDENT

• UNION removes not only duplicates between the two results but also duplicates within one side. The two CLERKs in department 20 become one.
• ORDER BY is written only once, after the entire set operation. When we put it after the first SELECT, we got ORA-03048 in the practice container. The textbook lists it as ORA-00933.

Boundaries of Conditions

BETWEEN Includes Both Endpoints

BETWEEN a AND b is >= a AND <= b. MARTIN and WARD at 1250 and ALLEN at 1600 are all included.

SELECT ENAME, SAL FROM EMP WHERE SAL BETWEEN 1250 AND 1600 ORDER BY SAL, ENAME;

ENAME             SAL
---------- ----------
MARTIN           1250
WARD             1250
MILLER           1300
TURNER           1500
ALLEN            1600

• You must write the smaller value first. BETWEEN 1600 AND 1250 is >= 1600 AND <= 1250, so it returns 0 rows with no error.
• NOT BETWEEN a AND b is < a OR > b. The boundary values are excluded from the result.

_ and ESCAPE in LIKE

% is zero or more characters, and _ is exactly one character. These are the employees whose second letter is A.

SELECT ENAME FROM EMP WHERE ENAME LIKE '_A%' ORDER BY ENAME;

ENAME
----------
JAMES
MARTIN
WARD

To find _ itself, put the character designated by ESCAPE in front of it to turn off its wildcard meaning. We apply two patterns to the three values 'A_B', 'AXB', and 'AB'.

SELECT V FROM (SELECT 'A_B' AS V FROM DUAL UNION ALL SELECT 'AXB' FROM DUAL UNION ALL SELECT 'AB' FROM DUAL) WHERE V LIKE 'A_B' ORDER BY V;

V
---
AXB
A_B

SELECT V FROM (SELECT 'A_B' AS V FROM DUAL UNION ALL SELECT 'AXB' FROM DUAL UNION ALL SELECT 'AB' FROM DUAL) WHERE V LIKE 'A\_B' ESCAPE '\' ORDER BY V;

V
---
A_B

• The ESCAPE character is not fixed to \. With ESCAPE '#', you write 'A#_B'. Without an ESCAPE clause, \ is just an ordinary character.

Tomorrow we will pair up and organize how the same SQL differs between Oracle and SQL Server (string concatenation, Top N, date functions, and so on).

📖 Glossary

• Logical execution order : The order in which the clauses of a SELECT statement are processed. It is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.
• ORA-00934 : The error when an aggregate function is used where it is not allowed, such as in WHERE.
• ORA-00979 : The error when a column not in GROUP BY is used in SELECT or HAVING without an aggregate.
• Cartesian product : The result of pairing every row of two tables with every other without a join condition. The row count is the product of the two tables' row counts.
• (+) : Oracle's outer join notation. It is attached to the column of the side that will be padded with NULL when there is no match.
• ORA-01427 : The error when a subquery after a single-row operator returns two or more rows.
• ESCAPE : The clause that designates the marker character that makes % and _ in a LIKE pattern be searched for literally.