Day 5 - Standard Joins (ANSI)

📌 Key Takeaways

• ANSI notation writes the join condition in ON of the FROM clause, leaving only row-filtering conditions in WHERE.
• NATURAL JOIN does an EQUI join on all columns with the same name, and USING does it on the columns you specify. In both cases, adding a prefix to that column is an error (ORA-25155, ORA-25154).
• CROSS JOIN is a Cartesian product. You cannot attach ON to it.
• LEFT, RIGHT, and FULL OUTER JOIN keep every row of the table on the side named by the direction. FULL cannot be written with Oracle's (+).
• In an outer join, a condition on the inner table is a pairing condition if you put it in ON and a result filter if you put it in WHERE. The number of result rows differs.

INNER JOIN ... ON

Moving yesterday's EQUI join to ANSI notation, you use JOIN instead of a comma and ON instead of WHERE.

SELECT E.ENAME, D.DNAME
FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE E.DEPTNO = 10
ORDER BY E.ENAME;

ENAME      DNAME
---------- --------------
CLARK      ACCOUNTING
KING       ACCOUNTING
MILLER     ACCOUNTING

• INNER can be omitted. Writing just JOIN means INNER JOIN.
• The join condition (ON) and the ordinary condition (WHERE) are split into different clauses, so it is easy to see whether you left out a condition.
• ON can also take conditions other than =. A Non-EQUI join can be expressed in ANSI too.

If you write only JOIN and give no join condition, it is a syntax error. If you need a Cartesian product, use CROSS JOIN.

SELECT COUNT(*) FROM EMP JOIN DEPT;

SELECT COUNT(*) FROM EMP JOIN DEPT
                                 *
ERROR at line 1:
ORA-02000: missing ON or USING keyword

NATURAL JOIN

NATURAL JOIN finds all the columns with the same name in the two tables and connects rows whose values in those columns are equal. You do not write a condition separately.

SELECT ENAME, DEPTNO, DNAME FROM EMP NATURAL JOIN DEPT WHERE DEPTNO = 10 ORDER BY ENAME;

ENAME          DEPTNO DNAME
---------- ---------- --------------
CLARK              10 ACCOUNTING
KING               10 ACCOUNTING
MILLER             10 ACCOUNTING

• The only column EMP and DEPT have in common is DEPTNO, so it becomes the same join as E.DEPTNO = D.DEPTNO.
• The common column DEPTNO is merged into one in the result. That is why writing DEPTNO without a prefix does not raise ORA-00918.

Looking at it with SELECT * reveals the merged form.

SELECT * FROM DEPT NATURAL JOIN EMP WHERE EMPNO = 7839;

    DEPTNO DNAME          LOC                EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM
---------- -------------- ------------- ---------- ---------- --------- ---------- ---------- ---------- ----------
        10 ACCOUNTING     NEW YORK            7839 KING       PRESIDENT            1981-11-17       5000

SELECT * FROM DEPT D JOIN EMP E ON D.DEPTNO = E.DEPTNO WHERE EMPNO = 7839;

    DEPTNO DNAME          LOC                EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- -------------- ------------- ---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
        10 ACCOUNTING     NEW YORK            7839 KING       PRESIDENT            1981-11-17       5000                    10

• NATURAL JOIN: the common column DEPTNO appears only once at the front, followed by the remaining columns. That is 10 columns.
• ON join: the DEPTNO of both sides appears. That is 11 columns.

Do Not Put a Prefix on the Common Column

The merged DEPTNO is not a column of either one table. Attaching a table alias is an error.

SELECT E.ENAME, E.DEPTNO FROM EMP E NATURAL JOIN DEPT D;

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

• E.ENAME is not a common column, so a prefix is fine. What caused the problem is E.DEPTNO.

If there is no common column at all, NATURAL JOIN becomes a join with no condition, that is, a Cartesian product.

SELECT COUNT(*) FROM EMP NATURAL JOIN SALGRADE;

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

• EMP and SALGRADE have no columns with the same name. It is 14 × 5 = 70 rows.
• Conversely, if there is an unintended column with the same name (for example a NAME that both tables have), that column also goes into the condition. The result of a NATURAL JOIN depends on column names.

USING

USING lets you choose directly which of the same-named columns to use for the join. You write the column name in parentheses.

SELECT ENAME, DEPTNO, DNAME FROM EMP JOIN DEPT USING (DEPTNO) WHERE DEPTNO = 10 ORDER BY ENAME;

ENAME          DEPTNO DNAME
---------- ---------- --------------
CLARK              10 ACCOUNTING
KING               10 ACCOUNTING
MILLER             10 ACCOUNTING

A column used in USING is also merged into one, like in NATURAL JOIN, so you cannot attach a prefix. It is the same in SELECT and in WHERE.

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

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

• NATURAL JOIN and USING can only do EQUI joins on same-named columns. Columns with different names or Non-EQUI conditions can only be written with ON.

CROSS JOIN

CROSS JOIN is used when you create a Cartesian product on purpose.

SELECT COUNT(*) FROM EMP CROSS JOIN DEPT;

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

It is a join that takes no condition, so attaching ON is a syntax error.

SELECT COUNT(*) FROM EMP CROSS JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;

SELECT COUNT(*) FROM EMP CROSS JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO
                                         *
ERROR at line 1:
ORA-03048: SQL reserved word 'ON' is not syntactically valid following '...(*) FROM EMP CROSS JOIN DEPT '

• Writing a condition in WHERE after CROSS JOIN is possible. Then the result becomes the same as an EQUI join.

OUTER JOIN — LEFT, RIGHT, FULL

The direction points to the left table or the right table relative to the JOIN keyword. All rows on that side are kept even without a match.

SELECT D.DEPTNO, D.DNAME, E.ENAME
FROM DEPT D LEFT OUTER JOIN EMP E ON D.DEPTNO = E.DEPTNO
WHERE D.DEPTNO IN (10, 40)
ORDER BY D.DEPTNO, E.ENAME;

    DEPTNO DNAME          ENAME
---------- -------------- ----------
        10 ACCOUNTING     CLARK
        10 ACCOUNTING     KING
        10 ACCOUNTING     MILLER
        40 OPERATIONS

• Department 40 on the left (DEPT) was kept without a match. It is the same result as yesterday's E.DEPTNO(+) = D.DEPTNO.
• OUTER can be omitted. LEFT JOIN means the same thing.

SELECT COUNT(*) FROM EMP E RIGHT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

  COUNT(*)
----------
        15

SELECT COUNT(*) FROM EMP E FULL OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

  COUNT(*)
----------
        15

• LEFT and RIGHT are equivalent when you swap the table order. A LEFT JOIN B is B RIGHT JOIN A.
• FULL is "rows with a match on both sides + rows only on the left + rows only on the right". In this data no employee is without a department, so only DEPT 40 is added, making 15 rows.

Outer joins are also used to pick out only the rows without a match. The rows where the key on the NULL-filled side is NULL are the rows without a match.

SELECT D.DNAME FROM DEPT D LEFT JOIN EMP E ON D.DEPTNO = E.DEPTNO WHERE E.EMPNO IS NULL;

DNAME
--------------
OPERATIONS

ANSI joins and (+) cannot be mixed in one query.

SELECT COUNT(*) FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO WHERE E.DEPTNO(+) = D.DEPTNO;

SELECT COUNT(*) FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO WHERE E.DEPTNO(+) = D.DEPTNO
                                                                    *
ERROR at line 1:
ORA-25156: old style outer join (+) cannot be used with ANSI joins

In ON or in WHERE?

In an INNER JOIN, the result is the same whether you put a condition in ON or in WHERE. In an outer join it is different. Here the situation is that you want to attach the employees with a salary of 3000 or more by department, but still show every department.

SELECT D.DNAME, E.ENAME, E.SAL
FROM DEPT D LEFT JOIN EMP E ON D.DEPTNO = E.DEPTNO AND E.SAL >= 3000
ORDER BY D.DNAME, E.ENAME;

DNAME          ENAME             SAL
-------------- ---------- ----------
ACCOUNTING     KING             5000
OPERATIONS
RESEARCH       FORD             3000
RESEARCH       SCOTT            3000
SALES

SELECT D.DNAME, E.ENAME, E.SAL
FROM DEPT D LEFT JOIN EMP E ON D.DEPTNO = E.DEPTNO
WHERE E.SAL >= 3000
ORDER BY D.DNAME, E.ENAME;

DNAME          ENAME             SAL
-------------- ---------- ----------
ACCOUNTING     KING             5000
RESEARCH       FORD             3000
RESEARCH       SCOTT            3000

• If you put a condition on the inner table (EMP) in WHERE, the NULL-filled rows disappear and it effectively becomes an INNER JOIN. It is the same phenomenon as the case we saw yesterday where (+) was left off E.SAL > 2900.

⚠️ Trap: Conversely, if you put a condition on the kept side (DEPT) in ON, rows do not decrease. ON D.DEPTNO = E.DEPTNO AND D.DEPTNO = 10 means "only pair department 10 with employees". The other departments all remain in the result, and only the employee columns become NULL. To filter the rows of the kept side, write it in WHERE.

SELECT D.DNAME, E.ENAME
FROM DEPT D LEFT JOIN EMP E ON D.DEPTNO = E.DEPTNO AND D.DEPTNO = 10
ORDER BY D.DNAME, E.ENAME;

DNAME          ENAME
-------------- ----------
ACCOUNTING     CLARK
ACCOUNTING     KING
ACCOUNTING     MILLER
OPERATIONS
RESEARCH
SALES

SELECT D.DNAME, E.ENAME
FROM DEPT D LEFT JOIN EMP E ON D.DEPTNO = E.DEPTNO
WHERE D.DEPTNO = 10
ORDER BY D.DNAME, E.ENAME;

DNAME          ENAME
-------------- ----------
ACCOUNTING     CLARK
ACCOUNTING     KING
ACCOUNTING     MILLER

• The ON version has 6 rows and the WHERE version has 3. In the ON version, the employee columns of RESEARCH and SALES are empty even though they have employees. That is because they are not department 10, so no match was accepted.

If you GROUP BY the outer join result, you can even get the headcount of 0 for a department with no employees.

SELECT D.DNAME, COUNT(E.EMPNO) AS CNT
FROM DEPT D LEFT JOIN EMP E ON D.DEPTNO = E.DEPTNO
GROUP BY D.DNAME
ORDER BY D.DNAME;

DNAME                 CNT
-------------- ----------
ACCOUNTING              3
OPERATIONS              0
RESEARCH                5
SALES                   6

• When we ran GROUP BY with only EMP in Day 1, department 40 did not appear. If you outer join with DEPT as the base, OPERATIONS 0 shows up.

Next week we cover subqueries, which put a query inside a query.

📖 Glossary

• ANSI join: The standard notation that writes the join condition as JOIN ... ON in the FROM clause.
• INNER JOIN: A join that keeps only rows that have a match meeting the condition. INNER can be omitted.
• NATURAL JOIN: Automatically does an EQUI join on all columns with the same name. If there is no common column, it is a Cartesian product.
• USING: Does an EQUI join on columns you choose from among the same-named columns. You cannot attach a prefix to those columns.
• CROSS JOIN: A join that pairs every row with every row without a condition. The result is a Cartesian product.
• LEFT / RIGHT OUTER JOIN: A join that keeps all rows of the left / right table even without a match.
• FULL OUTER JOIN: A join that keeps all rows of both tables. It cannot be written with Oracle's (+).
• ORA-25155 / ORA-25154: The errors raised when you attach a prefix to a NATURAL JOIN common column / a USING column.