ANSI Joins in Oracle: JOIN … ON, USING and Lost Rows
All lessons
- ANSI Joins in Oracle: JOIN … ON, USING and Lost RowsFree
- Old-Style Comma Joins and the Cartesian Product Trap Locked
- Non-Equi Joins: Matching Rows to Ranges with BETWEEN Locked
- Outer Joins: LEFT, RIGHT, FULL and Oracle's (+) Operator Locked
- Self Joins: Employees Next to Their Managers Locked
- Single-Row Subqueries: Compare Against a Computed Value Locked
- Multi-Row Subqueries: IN, ANY and ALL Locked
- Correlated Subqueries: Compare Each Row With Its Own Group Locked
- EXISTS, NOT EXISTS and the NOT IN NULL Trap Locked
- Top-N Queries: ROWNUM, Inline Views and FETCH FIRST Locked
- Hierarchical Queries: START WITH, CONNECT BY and LEVEL Locked
- Paths, Leaves and Subtrees: SYS_CONNECT_BY_PATH and CONNECT_BY_ISLEAF Locked
- Walking Up the Tree and Pruning Branches Locked
- Recursive WITH: Anchor, Recursive Member and Running Values Locked
- CONNECT_BY_ROOT, Several Roots and Cycles Locked
- ROLLUP: Subtotals and Grand Totals in One Query Locked
- CUBE and GROUPING SETS: Every Subtotal You Ask For Locked
- Analytic Functions: SUM OVER, PARTITION BY and RATIO_TO_REPORT Locked
- Ranking: RANK, DENSE_RANK, ROW_NUMBER and Top-N per Group Locked
- LAG, LEAD and Window Frames: Changes, Running Totals, Moving Averages Locked
- MERGE: Update and Insert in One Statement Locked
- Final Project: A Department Scorecard With Joins and Analytics Locked
You are not signed in — the lab works, but your progress is not saved. Sign up
Read first
The SQL
SELECT d.dept_id, d.dept_name,
SUM(e.salary) AS payroll,
ROUND(AVG(e.salary)) AS avg_salary
FROM employees e
JOIN departments d
ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY d.dept_id;Prefix every column with its table alias (e.salary, d.dept_name). In a join, an unprefixed column that exists in both tables raises ORA-00918: column ambiguously defined.
Loading the lab…